Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hey this worked.
    Thank You!