Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

YTD Cumulative for weekly figures

  I am trying to create measure for the cumulative columes. Below is the DAX code I wrote. As you can see in the last colume of the visual, it is still showing by week numbers not cumulative by week...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

hi  Anonymous 

For TOTALYTD is Time Intelligence Function, you need a calendar date table for it.

https://radacad.com/do-you-need-a-date-dimension

 

and for your case, just adjust the fact date field by calendar date filed and then it will works well. 

 

by the way, if you don't want add a calendar table, you could custom code the TOTALTYD measure by this formula

Measure =
CALCULATE (
    SUM ( 'Table'[Tonnes] ),
    FILTER (
        ALL ( 'Table'[Week ending], 'Table'[Date] ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
            && YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
    ),
    KEEPFILTERS ( 'Table'[Year] = 2020 )
)

 

Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Wow! The formula is working! This is such a relief after so many failed tries on the weekend.

     

    One small question, now the formula is returning weekly cumulative numbers for 2020. How do I modify the formula to retrieve the same for 2019?

     

    Thanks.

     

    Jen

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Wow! The formula is working! This is such a relief after so many failed tries on the weekend.

     

    One small question, now the formula is returning weekly cumulative numbers for 2020. How do I modify the formula to retrieve the same for 2019?

     

    Thanks.

     

    Jen

    • Pmorg73's avatar
      Pmorg73
      Icon for Post Patron rankPost Patron
      6 years ago

      If you are using my solution it is driven by the last date selected, so use a slicer for year. Select the year and works it out for that period.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I need both years side by side. Slider is more interactive. Jen