Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I know there are a lot of answers on related topics already, but they all seem focused more on overall cumulative totals or on specific scenarios.
At a conceptual level, I've got some simple fields: EventDateTime and Value being pulled from a SQL Server query.
If I want to set up a report that can easily do rolling window calculations on these and ideally be able to flip between different windows and calculations (e.g. rolling 30 days sum of Value, or rolling 6 month count of Value).
What's the cleanest way to set that up from the beginning? Should I bring in a generic Calendar table and join to that in my query so that the EventDateTime is associated with a simpler date field? Should I add some new calculated columns in DAX?
In something like python's pandas library, the rolling sum function makes this super simple.
http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.rolling_sum.html
In PowerBI, there appear to be a number of messy ways to do it. Any advice on what to start with and what the DAX syntax would be for the proposed solution?
Solved! Go to Solution.
Hi @pswigert,
If I want to set up a report that can easily do rolling window calculations on these and ideally be able to flip between different windows and calculations (e.g. rolling 30 days sum of Value, or rolling 6 month count of Value).
I don't aware any clean way to set this up in Power BI Desktop. Firstly, you may need to create different measures for "rolling 30 days sum of Value", "rolling 6 month count of Value", and other calculations you want to do. Then you should be able to follow steps below to create a new measure which can flip between different calculations according to your selection on a Slicer.
1. Import a new table with a column called "Calculation" like below.
2. Create a new measure using SWITCH Function (DAX) like below.
Measure = SWITCH ( FIRSTNONBLANK ( 'Table1'[Calculation], 1 ), "rolling 30 days sum of Value", measure1, "rolling 6 month count of Value", measure2 )
3. Show the measure above on the report with a Slicer of "Calculation" column.
Regards
Hi @pswigert,
If I want to set up a report that can easily do rolling window calculations on these and ideally be able to flip between different windows and calculations (e.g. rolling 30 days sum of Value, or rolling 6 month count of Value).
I don't aware any clean way to set this up in Power BI Desktop. Firstly, you may need to create different measures for "rolling 30 days sum of Value", "rolling 6 month count of Value", and other calculations you want to do. Then you should be able to follow steps below to create a new measure which can flip between different calculations according to your selection on a Slicer.
1. Import a new table with a column called "Calculation" like below.
2. Create a new measure using SWITCH Function (DAX) like below.
Measure = SWITCH ( FIRSTNONBLANK ( 'Table1'[Calculation], 1 ), "rolling 30 days sum of Value", measure1, "rolling 6 month count of Value", measure2 )
3. Show the measure above on the report with a Slicer of "Calculation" column.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |