Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Day All,
I am looking for some support with some DAX that I am struggling to piece together. I am looking within a matrix to sum together each weeks sales & and each weeks sales to the previous weeks sales. I have built a matrix as displayed below.
From the table I have included the following DAX.
Volume BU Rolling FYTD =
CALCULATE(sum(EPOS[Volume BU]),
FILTER(ALL('Date'[Week End Date]),
'Date'[Week End Date] <= MAX('Date'[Week End Date])
)
)
This works in that it gives me the sum of each week but I need to have a rolling cumulative sum for each week.
For Example Week1 = £2000, Week 2 = £1000, so the cumlative for Week 2 would be £3000. & so on to the end of the year to date.
I am guessing that this should be relatively straightforwards but I have not been able to resolve from my limited DAX knowledge.
I have tried various examples in the forums and sum of the pre defined calculations in quick measures but with no success.
Can anyone advise me on how to achieve this.
Thanks
alforc1
Solved! Go to Solution.
HI @alforc1,
If you want to get YTD cumulative running total, I think you need to add a year condition to control formula work on same year range.
Volume BU Rolling FYTD = CALCULATE ( SUM ( EPOS[Volume BU] ), FILTER ( ALLSELECTED ( 'Date'[Week End Date] ), YEAR ( 'Date'[Week End Date] ) = YEAR ( MAX ( 'Date'[Week End Date] ) ) && 'Date'[Week End Date] <= MAX ( 'Date'[Week End Date] ) ) )
Regards,
Xiaoxin Sheng
Hi, I have a further question in relation to the the previous. The dax works up until the end of the current year but I need the cumulative total to also run into the next year. can you advise how I would amend the DAX below to roll over into the following year. So at the end of 2017 the cumulative resets but I would like it to continue into 2018.
Any help would be appreciated.
Volume BU Rolling FYTD =
CALCULATE (
SUM ( EPOS[Volume BU] ),
FILTER (
ALLSELECTED ( EPOS[Week End Date]),
YEAR ( EPOS[Week End Date] ) = YEAR ( MAX ( EPOS[Week End Date] ) )
&& EPOS[Week End Date] <= MAX ( EPOS[Week End Date] )
)
Regards
Chris
)
Cumulative Volume =
CALCULATE (
SUM ( EPOS[Volume BU]),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'EPOS'[Week End Date] )
)
)
Make sure in the table/matrix you use the week from the Date table - and make sure your date table is marked as a date table.
HI @alforc1,
If you want to get YTD cumulative running total, I think you need to add a year condition to control formula work on same year range.
Volume BU Rolling FYTD = CALCULATE ( SUM ( EPOS[Volume BU] ), FILTER ( ALLSELECTED ( 'Date'[Week End Date] ), YEAR ( 'Date'[Week End Date] ) = YEAR ( MAX ( 'Date'[Week End Date] ) ) && 'Date'[Week End Date] <= MAX ( 'Date'[Week End Date] ) ) )
Regards,
Xiaoxin Sheng
That has worked perfectly. You have saved my day.
Thank you for answering Xiaoxin Sheng.
Regards
Chris
Hi, I have a further question in relation to the the previous. The dax works up until the end of the current year but I need the cumulative total to also run into the next year. can you advise how I would amend the DAX below to roll over into the following year. So at the end of 2017 the cumulative resets but I would like it to continue into 2018.
Any help would be appreciated.
Volume BU Rolling FYTD =
CALCULATE (
SUM ( EPOS[Volume BU] ),
FILTER (
ALLSELECTED ( EPOS[Week End Date]),
YEAR ( EPOS[Week End Date] ) = YEAR ( MAX ( EPOS[Week End Date] ) )
&& EPOS[Week End Date] <= MAX ( EPOS[Week End Date] )
)
Regards
Chris
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |