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

[hcova7]

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

[hcova7]

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=10086...

[pcoley · Accepted solution]

@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😀

[hcova7]

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

[vivek31]

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.

[vivek31]

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.

[Anonymous]

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.

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[hcova7]

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=10... 

[lbendlin]

Access denied, please check.

[hcova7]

https://docs.google.com/spreadsheets/d/1vJ5slHBAnB46JB7i_F7Vp47zfSG1DkG7/edit?usp=drive_link&ouid=10...

[lbendlin]

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

[hcova7]

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.

[lbendlin]

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