How to solve this DAX measure Running Total problem?

I have the following Excel pivot table:

Image

This Pivot Table is build from the following table called "Systems"

Image

I add a Calendar Table into the model too

Image

Following with the above Pivot Table picture, I have tried to write the below DAX measure to calculate the Running Total values (Column E in the above Pivot Table) related with column "Total".

As you can see in the above Pivot Table the RTotal DAX measure applied in column E didn't work well.


Image

Image

So what do I need???

1 ) I would like ask you about DAX measures the get the columns "Yearly RTotal" (accumulates the year results of "Total" only) and "AllTime RTotal" (accumulates the entire pivot table results of "Total") in the below Pivot Table

Image

2) Moreover I need that these DAX measures work well when I try to filter by "Year" column too.

For example, if I filter for 2022 and 2023 years, I would like to get the following Pivot Table

Image

|

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

This measure pattern should work

YTD Total = calculate([Total],datesytd(calendar[date]))

All time total = calculate([Total],datesbetween(calendar[date],minx(allselected(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated December 13, 2024 Views 36 Applies to: