"Events In Progress"? - how to tackle using Power Query

"Events In Progress" - how to tackle using Power Query

Wyn Hopkins Wyn Hopkins

Wyn Hopkins

Solving data challenges for companies and people. Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Part of the Amazing team at Access Analytic Microsoft MVP ??6 Million+ YouTube views

发布日期: 2022年6月24日
+ 关注

If you have a start date and end date but need to calculate how many people or items were present between those dates then you can use Power Query to help with this.

This approach will work in Power BI and Excel

Source CSV file and Power BI solution is here


I've also done a video using DAX to solve this.


You may find the Power Query method means your data takes longer to refresh but there is likely an upside in the speed of your interactions between visuals in the report for your end users.

Wyn

Follow me on Linkedin

Other Platforms here: https://wyn.bio.link/

No alt text provided for this image
Moreno Milenkovi?

Data Analyst | Business & Supply Chain Analytics at Synergy Global Housing

2 年

Thanks for sharing Wyn! Just to add to the topic, so far I was calculating a table using DAX by CROSSJOIN-ing the Check in and Check out dates with a CALENDAR() calculated table for the period I needed and FILTER-ed to keep only dates that are between the check in and check out date. I'll give a try to the PowerQuery way now!

赞
回复
1 次回应
Jakob Viktor Albrechtsen

Production planning | Reporting and analysis | Process and systems development

2 年

Awesome, thanks for sharing, Wyn.

赞
回复
2 次回应
查看更多评论

要查看或添加评论，请登录

Wyn Hopkins的更多文章

  • The Best Dependent Drop Down Technique
    2024年11月15日

    The Best Dependent Drop Down Technique

    Driven by a gauntlet laid down by friend and fellow MVP Mark Proctor on our Unpivot podcast recently I revisited my…

    1 条评论
  • Should you use Measures for Conditional Formatting?
    2024年6月9日

    Should you use Measures for Conditional Formatting?

    Streamline Your Power BI Conditional Formatting with Measures I've always found the built-in conditional formatting in…

    6 条评论
  • Consolidate and Hyperlink to Excel files on SharePoint
    2024年1月3日

    Consolidate and Hyperlink to Excel files on SharePoint

    In this video, we're diving into the world of Excel and Power BI, focusing on creating hyperlinks for easy referencing…

    4 条评论
  • The greatest multi-level Excel drop-down list ever!
    2023年11月3日

    The greatest multi-level Excel drop-down list ever!

    One-Off dependent drop down lists in data validation are relatively straightforward: here's a technique using XLOOKUP…

    5 条评论
  • Default Your Slicer to the Current Month in Power BI
    2023年7月31日

    Default Your Slicer to the Current Month in Power BI

    First Published November 2022 on our YouTube Channel Join 65,000+ subscribers to stay up to date with new videos…

    4 条评论
  • Power BI Licensing Explained
    2023年5月27日

    Power BI Licensing Explained

    What are the differences between: Power BI Free (soon to be renamed Fabric Free) Pro: $10 USD pp/pm PPU (Premium Per…

    2 条评论
  • Power BI May 2023 Release
    2023年5月26日

    Power BI May 2023 Release

    The May version of Power BI desktop is out This is a very quick post highlighting my 2 favourite picks: 1. Azure Maps…

    5 条评论
  • Simpler DAX ? = Power Query
    2023年5月12日

    Simpler DAX ? = Power Query

    If you are struggling to write a complicated DAX formula then the answer may be to step back and do a little Power…

    2 条评论
  • Power BI Explained
    2023年3月29日

    Power BI Explained

    A simple explanation of Power BI I hope you find this useful. Power BI continues to go from strength to strength and…

  • SUMX explained.
    2023年2月27日

    SUMX explained.

    SUMX is a DAX function that can be used in an Excel Data Model (aka Power Pivot) to create a temporary column…

    6 条评论

社区洞察

其他会员也浏览了