Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

Image 2.PNG

15 REPLIES 15
v-lili6-msft
Community Support
Community 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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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
Not applicable

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

hi @Anonymous 

If so, just remove the conditional "KEEPFILTERS ( 'Table'[Year] = 2020 )" from the measure.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

En, we are getting very very close. 

 

After I removed the condition,

 

- The weeks that we have data in 2020 ( 2020 is not over yet), it's showing 2020 instead of 2019 cumulative figures. 

- The weeks that we don't have data in 2020, it is cumulating 2019 weekly numbers to 2020 running total uptil the last week in 2020. 

 

Another observation is that the colume total of 2019 cumulative is now showing the total of 2020 cumulative.

 

Appreciate your help. Wish you safe and healthy amist this unprecedent crisis.

 

Jen

hi  @Anonymous 

For your case, you'd better drag [Year] field into visuals. you need year-weekno dim in the visual.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I can't. The visual is what's been requested. Has to be exactly the same. I can't change the layout. Jen

Anonymous
Not applicable

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
Post Patron
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])))
Anonymous
Not applicable

Thanks a lot for your reply. Jen

Anonymous
Not applicable

I didn't actually. I had no idea what a Calendar is. A regular calendar? In columnar format? Add as a new colume?

 

Jen

Anonymous
Not applicable

Thanks a lot for your reply.

It would be worth your while quickly researching creating calendars (loads on youtube - check Enterprise DNA). It gives you a new table that you make a relationship to your data table with.  Then you have Time inteligence (as they call it) and you can measure against a set calendar range, using days, months, years or whatever.

 

eg

 

create Table button and enter this:

calendar = CALENDAR(DATE(2018,04,01),date(2021,03,31))
 
from here create columns for months, weeks, Financial year months, month numbers. Lots and lots of options that you adjust for your requirements. I typically have a month number; financial year month number; year; quarter, Day number; Day of week number
eg -
month number = month('calendar'[Date])
Day = DAY('calendar'[Date])
Anonymous
Not applicable

Checked calendar table out. Great! 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors