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.
Hi All,
I have the following table:
I'm looking for the required DAX such that column 'test 3' is the cumulative total of the prior daily targets. The daily target is a measure. I can't use a calculated column because the daily target is made of of various product lines and my users want to be able to slice performance against daily target by product line.
So how does one perform a cumulative measure of a daily target?
I've already read this: https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/ and it didn't help me.
Solved! Go to Solution.
Hi @Anonymous
Please Try this one
= SUMX ( FILTER ( ALLSELECTED ( TableName[Dates] ), TableName[Dates] < SELECTEDVALUE ( TableName[Dates] && WEEKDAY ( TableName[Dates], 2 ) IN { 1, 2, 3, 4, 5 } ) ), [Daily Target (measure)] )
Hi @Anonymous
Give this a shot please
= SUMX ( FILTER ( ALL ( TableName[Dates] ), TableName[Dates] < SELECTEDVALUE ( TableName[Dates] ) ), [Daily Target (measure)] )
Hi @Anonymous
Please Try this one
= SUMX ( FILTER ( ALLSELECTED ( TableName[Dates] ), TableName[Dates] < SELECTEDVALUE ( TableName[Dates] && WEEKDAY ( TableName[Dates], 2 ) IN { 1, 2, 3, 4, 5 } ) ), [Daily Target (measure)] )
This DAX throws an error for me. Column " cannot be found or may not be used in this expression.
I think what you meant is:
= SUMX ( FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= SELECTEDVALUE(Dates[Date]) && WEEKDAY ( Dates[Date], 2 ) IN { 1, 2, 3, 4, 5 } ), [Daily Target (meas)] )
I changed the '<' to '<=' on the dates comparator to get the cumulative sum to start on the first row.
This is an AWESOME solution, thank you ever so much!!
Hi @Zubair_Muhammad,
Thats >95% there! My Dates table has a concept of WorkDay... I need the cumulative for Workdays only, also the ALL filter on dates means the cumulative sum starts from several years earlier. My report has dates filtered, i'm really looking for the cumulative that meets the currently selected date range (i.e. starting from 1 Jan 2018 should ignore prior years targets).
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |