Forum Discussion

hcova7's avatar
hcova7
Frequent Visitor
1 year ago
Solved

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

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"   ...
  • pcoley's avatar
    pcoley
    1 year ago

    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's avatar
hcova7
Frequent Visitor
1 year ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

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