cancel
Showing results 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

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

15 REPLIES 15
Community Support

hi  @Anonymous

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

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

Post Patron

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

Community Support

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

Community Support

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

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

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

Post Patron

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors