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]),
    FILTER(
        ALLSELECTED('CalendarM'[Date]),
        ISONORAFTER('CalendarM'[Date], MAX('Schedule'[Forecasted Date]), DESC)
    )
)
 
However, the visual I have (1)skips the months with no value and (2) has blank value for some months.

I want a  running total for every month, if there is no value for that month, just carry the previous month's value forward. Would appreciate if someone can advise how to revise the DAX?

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
        )
    )

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi ElenaJ ,

    Could you share which table are you using to derived month column in your matrix?

     


    BR

    • ElenaJ's avatar
      ElenaJ
      Frequent Visitor
      4 years ago

      Sure. I created a ten year calendar table called calendarM (2020-2030) and linked it to my fact table schedule. Calendar M has date and month/year columns. 

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