Forum Discussion

ElenaJ's avatar
ElenaJ
Frequent Visitor
3 years ago
Solved

Running Total with Blank Month Value

Hi, I created a measure for a running total by month, the coding used is:   Cumulative = CALCULATE(     SUM('Schedule'[Forecast]),     USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your feedback.

    Please check the below if it suits your requirement.

     

    -Jun value shows in the table visualization

    -only upto Nov values are shown.

     

     

    Cumulative = 
VAR _lastdateinscheduletable =
    CALCULATE ( MAX ( Schedule[Forecasted Date] ), REMOVEFILTERS () )
RETURN
    CALCULATE (
        SUM ( 'Schedule'[Forecast] ),
        USERELATIONSHIP ( CalendarM[Date], 'Schedule'[Forecasted Date] ),
        FILTER (
            ALLSELECTED ( 'CalendarM' ),
            CalendarM[Date] <= MAX ( CalendarM[Date] )
                && CalendarM[Year] = MAX ( CalendarM[Year] )
                && MIN ( CalendarM[Date] ) <= _lastdateinscheduletable
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Thank you for your feedback.

Please check the below if it suits your requirement.

 

-Jun value shows in the table visualization

-only upto Nov values are shown.

 

 

Cumulative = 
VAR _lastdateinscheduletable =
    CALCULATE ( MAX ( Schedule[Forecasted Date] ), REMOVEFILTERS () )
RETURN
    CALCULATE (
        SUM ( 'Schedule'[Forecast] ),
        USERELATIONSHIP ( CalendarM[Date], 'Schedule'[Forecasted Date] ),
        FILTER (
            ALLSELECTED ( 'CalendarM' ),
            CalendarM[Date] <= MAX ( CalendarM[Date] )
                && CalendarM[Year] = MAX ( CalendarM[Year] )
                && MIN ( CalendarM[Date] ) <= _lastdateinscheduletable
        )
    )
haoqiong55's avatar
haoqiong55
Regular Visitor
2 years ago

This answer has helped resolve my question too. Much appreciated! Now I'm trying to get a new measure for calculating the running total of previous year in same way. Could you provide some guidance? Thank you