Forum Discussion
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 numbers. Second screen is the source data.
Hope you could give me some solution.
Jen
15 Replies
- Pmorg73
Post Patron
[my first attempt to answer a query for someone else!!] 🙂
I assume you already have a calendar set up? But replace the calendar[date] in my version for your date column and it should work for you.
this is a good cumulative measure that I use a lot if you do:
Tonnes 2019 cumulative = CALCULATE('scale data 2019 2020'[tonnes],FILTER(ALLSELECTED(calendar'[Date] ) ,'calendar'[Date] <= MAX('00_calendar'[Date]))) 
v-lili6-msftCommunity Support
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
- Pmorg73
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.
