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
        )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Cumulative =
CALCULATE (
    SUM ( 'Schedule'[Forecast] ),
    USERELATIONSHIP ( CalendarM[Date], 'Schedule'[Forecasted Date] ),
    FILTER (
        ALLSELECTED ( 'CalendarM' ),
        CalendarM[Date] <= MAX ( CalendarM[Date] )
            && CalendarM[Year] = MAX ( CalendarM[Year] )
    )
)
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