Hi,
Please help how to properly calculate the above question using the last 4 days. The rolling average must only sum up those days that have value.
I tried the common ways of computing rolling average/sum but the results don’t work when there are days that have no value.
If possible use only measures and not calculated column.
Below is the guide on what the value should be and blank rows or value must also be hidden.
Hello @JazZ0003,
Thank You for posting your query onto the Forum.
In order to achieve the results based on the scenario that you’ve specified. I’m not sure results can be achieved based on the measures itself. I added a calculated column which helps in calculating the “Cumulative/Running Totals Ignoring The Blanks”. This column does the ranking of the Date field which is there inside the SampleData (Fact Table) and then this “Ranking” column can be passed on inside the measure as a reference.
So below are the steps mentioned to achieve the results -
1). Create a calculated column which does the Ranking of the Dates ignoring the Blank rows -
Ranking =
VAR _Current_Date = SampleData[Date]
RETURN
CALCULATE( RANK.EQ( _Current_Date , SampleData[Date] , ASC ) ,
ALL( SampleData ) ,
SampleData[Value] <> BLANK() )
2). Create a measure which evaluates the SUM of results of Last 4 Days Running Totals -
4 Days Rolling Sum - Harsh =
IF( ( SELECTEDVALUE( SampleData[Ranking] ) - 4 ) >= 0 ,
CALCULATE( SUM( SampleData[Value] ) ,
FILTER( ALLSELECTED( SampleData ) ,
SampleData[Ranking] > MAX( SampleData[Ranking] ) - 4 &&
SampleData[Ranking] <= MAX( SampleData[Ranking] ) ) ) ,
BLANK() )
3). Create a measure which evaluates the AVERAGE of results of Last 4 Days Running Totals -
4 Days Rolling Averages - Harsh =
DIVIDE( [4 Days Rolling Sum - Harsh] , 4 , 0 )
Below is the screenshot of the final results provided for the reference -
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Note: Even after adding a calculated column, the size of the file is still small in comparison to the file which you have uploaded.
Thanks and Warm Regards,
Harsh
Rolling Sum and Average Ignoring Blanks - Harsh.pbix (67.1 KB)
Hi @Harsh
Thank you for taking a look a look into my problem. As I tried your measure, I noticed an inconsistency in the results which is a bit weird because an issue occurred around the middle of the data.
Those highlighted rows has wrong results.
Upon further digging, there are some inconsistencies in the raw data. I updated the SampleData and the issue was carried over to our sample PBIX file. Please see attached.
Added a slicer also to reflect actual data.
Hello @JazZ0003,
I see that you’re using another formula for referencing i.e., “Date Index”. And the reason why it didn’t provided the results is because you didn’t adjusted that formula based on the context that you’re supplying into the table visual in the form of slicer.
If you’re adding slicers or supplying any additional external context than you’ll also be required to adjust your formula in accordance with that.
In the “Date Index” formula, inside the “ALLEXCEPT()” function, you didn’t mentioned the condition that it should also affect the results based on “Category”. Once I added that condition inside the “ALLEXCEPT()” function, it provided the results as per the Excel screenshot.
Below is the corrected formula for Date Index column alongwith the screenshot of the final results provided for the reference -
I’m also attaching the working of the PBIX file for the reference.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Important Note: Currently, the formula is adjusted as per the provided scenario/circumstances. Please adjust the context accordingly, if in case you change your scenario.
Thanks and Warm Regards,
Harsh
Rolling Sum and Average Ignoring Blanks - Harsh v2.pbix (70.0 KB)
Thank you very much @Harsh,
This one solved my problem.
By the way, How you are able to keep your PBIX smaller than the one I sent?
We can close this thread by the way.
