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

    Thanks for the suggestion.

     

    My problem is that I created a 10 year calendar that link to the dates in the schedule table. While the schedule table only runs to a certain month in 2023. I want the running total to add up to the last date on the schedule table instead of the calendar table.

  • 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!