Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
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.
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
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
I can't. The visual is what's been requested. Has to be exactly the same. I can't change the layout. Jen
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
[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:
Thanks a lot for your reply. Jen
I didn't actually. I had no idea what a Calendar is. A regular calendar? In columnar format? Add as a new colume?
Jen
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:
Checked calendar table out. Great!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
89 | |
70 | |
66 |