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