Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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 , 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/
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))
This is the data set and canvas...
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
is the "week" week number or week starting date?
Hi @Anonymous ,
The return values of new created calculated column "Week" are week numbers.
Best Regards
Rena
It worked! Four days of struggle. Thank you!
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |