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.