topic Re: How to modify some DAX measures to solve a Running Total problem in DAX Commands and Tips

How to modify some DAX measures to solve a Running Total problem

hcova7 — Thu, 12 Dec 2024 14:11:05 GMT

Dear sirs, Please your help for the following DAX measure problem.

I have the following Excel pivot table:

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

I add a Calendar Table into the model too

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.

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

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

Re: How to modify some DAX measures to solve a Running Total problem

lbendlin — Thu, 12 Dec 2024 20:23:15 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to modify some DAX measures to solve a Running Total problem

Anonymous — Fri, 13 Dec 2024 08:29:15 GMT

Hi hcova7 ,

Based on the description, try using the following DAX formula to create measures.

YearlyRTotal = CALCULATE( [Total], FILTER( ALLEXCEPT(Systems, Systems[SysName], Calendar[Year]), Systems[Date/Time3] <= MAX(Systems[Date/Time3]) ) )

AllTimeRTotal = CALCULATE( [Total], FILTER( ALL(Systems), Systems[Date/Time3] <= MAX(Systems[Date/Time3]) ) )

If the DAX formula does not work, please provide a sample data file. The screenshot is too blurry to read.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to modify some DAX measures to solve a Running Total problem

vivek31 — Fri, 13 Dec 2024 10:22:15 GMT

HI
hcova7

1) you can like to try this Dax formula to create year running total.

Year_r_total = TOTALYTD([revenue],'Calendar'[DateKey])

2) second Dax formula is All time running total you can try this Dax formula.

All time r_total = CALCULATE([revenue], FILTER(ALL('Calendar'), 'Calendar'[DateKey] <= MAX('Calendar'[DateKey])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to modify some DAX measures to solve a Running Total problem

vivek31 — Fri, 13 Dec 2024 10:37:23 GMT

hi hcova7

1) you can like to try this Dax formula to create year running total.

Year_r_total = TOTALYTD([revenue],'Calendar'[DateKey])

2) second you can try this Dax formula to create All time running total.

All time r_total = CALCULATE([revenue], FILTER(ALL('Calendar'), 'Calendar'[DateKey] <= MAX('Calendar'[DateKey])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to modify some DAX measures to solve a Running Total problem

hcova7 — Mon, 16 Dec 2024 00:44:46 GMT

Here you have the excel worksheet with the data and the DAX measures

Regards

https://docs.google.com/spreadsheets/d/1C2wFF6ls0gXb-NwbKfFiTp_7H93K9jln/edit?usp=drive_link&ouid=100866832098495107517&rtpof=true&sd=true

Re: How to modify some DAX measures to solve a Running Total problem

lbendlin — Mon, 16 Dec 2024 14:43:01 GMT

Access denied, please check.

Re: How to modify some DAX measures to solve a Running Total problem

hcova7 — Mon, 16 Dec 2024 21:30:33 GMT

https://docs.google.com/spreadsheets/d/1vJ5slHBAnB46JB7i_F7Vp47zfSG1DkG7/edit?usp=drive_link&ouid=100866832098495107517&rtpof=true&sd=true

Re: How to modify some DAX measures to solve a Running Total problem

lbendlin — Mon, 16 Dec 2024 22:16:00 GMT

You can use a calendar table, and then create a quick measure for the running total

Re: How to modify some DAX measures to solve a Running Total problem

hcova7 — Mon, 16 Dec 2024 23:03:03 GMT

Hi Ibendlin.

The problem is that in my data table there months without any profit. So the measure I need must ensure that the running total doesn´t skip any month. I mean, for months with no profit, the running total must remain unchanged, "carrying forward" the previous total or the last calculated running total.
My DAX measure calculates a wrong running total after a months without no profit data.

Re: How to modify some DAX measures to solve a Running Total problem

lbendlin — Mon, 16 Dec 2024 23:15:33 GMT

Have you checked the Quick Measure? It seems to work as you need it to, showing the right number for each time interval.

Re: How to modify some DAX measures to solve a Running Total problem

hcova7 — Wed, 25 Dec 2024 18:48:14 GMT

Hi there.

I found a solution to my problem. Near to perfect.

The solution works perfect and it generates perfect values too. However I believe that I am doing a mistake in the way I am coding the DAX measure.

For my Yearly RTotal (a YTD version) I wrote the following code in the System table

Yearly RTotal =

=CALCULATE( [Total], DATESYTD( 'Calendar'[Date] ) )

where Total is:

Total =SUM(System[ProfitUSD])

For my All-Time RTotal I wrote the following code in the Calendar table

All-Time RTotal =

VAR
     CurrentDate = MAX('Calendar'[Date])
VAR
     StartDate = CALCULATE(MIN('Calendar'[Date]), ALL('System'))
VAR
     EndDate = CALCULATE(MAX('Calendar'[Date]), ALLSELECTED('Calendar'))
VAR
     RunningTotal =
     CALCULATE(
          SUM('System'[ProfitUSD]),
          FILTER(
               ALL('Calendar'),
               'Calendar'[Date] <= CurrentDate
          )
     )
RETURN
IF(
     CurrentDate < StartDate || CurrentDate > EndDate,
     BLANK(), -- Return BLANK() for dates outside the timeline bounds
     COALESCE(RunningTotal, 0)
)

The problem, and here where I beleive that problem arises, is that I need to insert 2 timelines to work properly.

Let me clarify this with the below pivot table.

In the above picture "Date" timeline comes from "Calendar" table and it takes control over the Pivot table "Year" column, filtering dates from 2011-2014. "Date/Time_3" timeline comes from "System" table and take control of the Yearly and All-time RTotal. In other words, if I delete the "Date" timeline I will get wrong results, as shown below. (wrong calculations in column "All-Time RTotal")

Conversely, if I delete "Data/Time_3" timeline, I get the following pivot table:

As you can realize, it works wrong too.

Finally, can anybody let me know how to modify the above shown measure so I can work with one Timeline only instead of 2?

Here is the Excel file link

https://docs.google.com/spreadsheets/d/14FTX6A5FxZ4i58z_0ilpUeidWBP4qleC/edit?usp=sharing&ouid=100866832098495107517&rtpof=true&sd=true

Re: How to modify some DAX measures to solve a Running Total problem

pcoley — Thu, 26 Dec 2024 20:02:17 GMT

hcova7
Please try with this measure:

RTotal_6 := VAR _CurrentDate = MAX( 'Calendar'[Date] ) VAR _StartDate = CALCULATE( MIN( 'Calendar'[Date] ); ALLSELECTED( 'Calendar' ) ) VAR _EndDate = CALCULATE( MAX( 'Calendar'[Date] ); ALLSELECTED( 'Calendar' ) ) VAR _Range = DATESBETWEEN( 'Calendar'[Date]; _StartDate; _CurrentDate ) VAR _RunningTotal = CALCULATE( SUMX( System; 'System'[ProfitUSD] ); _Range ) RETURN _RunningTotal

I hope this help, if so please mark as a solution. Kudos are welcome😀

Re: How to modify some DAX measures to solve a Running Total problem

hcova7 — Sun, 29 Dec 2024 18:55:05 GMT

Thank you very much for your perfect solution. You don't know how much time I have been able to reduce with this measure. Warm regards