Forum Discussion

ElenaJ's avatar
ElenaJ
Frequent Visitor
4 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
    4 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
        )
    )
ElenaJ's avatar
ElenaJ
Frequent Visitor
4 years ago

For example, in the schedule table, 7if we don't have any June dates in the Forecasted Date, how can we calculate a culmulative value without skipping June?

 

Forecasted DateForecast

Sunday, 1 May 20225
  
Friday, 1 July 20223
Monday, 1 August 20224
Thursday, 1 September 20227
Saturday, 1 October 20226
Tuesday, 1 November 20229
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 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
        )
    )
  • ElenaJ's avatar
    ElenaJ
    Frequent Visitor
    4 years ago

    It worked! Amazing!

    Many thanks for your help!

  • 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