Forum Discussion
DAX function to get Cumulative Subtraction
Here I have scenario where I have following values
1. Total TestRuns =28
2. Number of working days = 5
3. Date Range
Using which I need to generate the Expected Output which is highlighted in Orange color column where Total Runs count keeps decereasing by 5.6 as the date changes.
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Rank = RANKX( 'Table','Table'[Date],,ASC)Flag = var _workingdays= COUNTX(FILTER(ALL('Table'), NOT(WEEKDAY('Table'[Date],2)) in {6,7}),[Date]) var _nooftestruns=28 var _value1=DIVIDE(28,5) return IF( 'Table'[Rank] = MINX(ALL('Table'),[Rank]), _nooftestruns, - _value1)Output = SUMX( FILTER(ALL('Table'), 'Table'[Rank]<=EARLIER('Table'[Rank])),[Flag])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Rank = RANKX( 'Table','Table'[Date],,ASC)Flag = var _workingdays= COUNTX(FILTER(ALL('Table'), NOT(WEEKDAY('Table'[Date],2)) in {6,7}),[Date]) var _nooftestruns=28 var _value1=DIVIDE(28,5) return IF( 'Table'[Rank] = MINX(ALL('Table'),[Rank]), _nooftestruns, - _value1)Output = SUMX( FILTER(ALL('Table'), 'Table'[Rank]<=EARLIER('Table'[Rank])),[Flag])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly