YTD Cumulative for weekly figures

[Anonymous]

	Tonnes Last Year	Tonnes This Year	Tonnes Last Year Cumulative	Tonnes This Year Cumulative
Week 1	250	238	250	238
Week 2	300	526	550	764
Week 3	125	114	675	878
Week 4	382	538	1057	1416
Week 5	405	410	1462	1826
Week 6	508	235	1970	2061
Week 7	137	233	2107	2294

I am trying to create measure for the cumulative columes. 

I have following ...

Tonnes 2020 cumulative = TotalYTD(sum('data table'[Tonnes]),'data table'[date],if('data table'[Year]=2020,2020,0))

It's returning weekly numbers by the week. Not YTD culmulative. 

Appreciate your help as always.

Jen

[amitchandak]

@Anonymous , Not sure on the need to if clause,

You should try like

Tonnes 2020 cumulative = TotalYTD(sum('data table'[Tonnes]),'data table'[date])

Prefer with a date table

Tonnes 2020 cumulative = TotalYTD(sum('data table'[Tonnes]),'Date'[date])

And Create week in your date table and use from there.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[karun_r]

Do you have a sample PBI that you can share to help us understand more

From what I can see, this should work. Please share a sample PBI if this is not working. Also you should have a seperate time dimension table for the time intelligence calculations to work properly. That's why I replaced your 'Data Table' with 'Time'  in my version.

Total YTD Cummulative  =  TOTALYTD(SUM('Data Table'[Tonnes], 'Time'[Date], KEEPFILTERS('Time'[Year] = 2020))

[Anonymous]

This is the data set and canvas...

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please create one calculated column to get the week number first, then create one measure to cumulative the weekly tonnes. The related formulas as below:

Week = WEEKNUM('Scale date 2019 2020'[Date],2)

Tonnes 2019 cumulative = 
VAR curweek =
    MAX ( 'Scale date 2019 2020'[Week] )
RETURN
    CALCULATE (
        SUM ( 'Scale date 2019 2020'[Tonnes] ),
        FILTER (
            ALL ( 'Scale date 2019 2020' ),
            YEAR ( 'Scale date 2019 2020'[Date] ) = 2019
                && 'Scale date 2019 2020'[Week] <= curweek
        )
    )

Best Regards

Rena

[Anonymous]

is the "week" week number or week starting date?

[v-yiruan-msft]

Hi @Anonymous ，

The return values of new created calculated column "Week" are week numbers.

Best Regards

Rena

[Anonymous]

It worked! Four days of struggle. Thank you!