YTD Cumulative for weekly figures

[Anonymous]

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

[v-lili6-msft]

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]

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]

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]

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

[v-lili6-msft]

hi @Anonymous 

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

Regards,

Lin

[Anonymous]

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

[v-lili6-msft]

hi  @Anonymous 

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

Regards,

Lin

[Anonymous]

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

[Anonymous]

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]

[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]

Thanks a lot for your reply. Jen

[Anonymous]

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]

Thanks a lot for your reply.

[Pmorg73]

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]

Checked calendar table out. Great!