DAX measure to calculate Descending running total of scores by Date

[wmcintosh]

I have a table of Risks, each with a Pre and Post-action score.

I need to chart the reduction in Risk score by Completion date. Based on the above this would give me the following:

... which I could then represent as:

Seems simple enough but how do I get the 'Total minus Risk Score by completion date' as a measure?
I have tried the following:

Pre Mitigation Risk Score minus Risk Score Delta running total in Date =

CALCULATE(
SUM('Risks (Project)'[Pre Mitigation Risk Score]) - [Risk Score Delta],

    FILTER(

        ALLSELECTED('Calendar'[Date]),

        ISONORAFTER('Calendar'[Date], MIN('Calendar'[Date]), ASC)

    )

)

My Risks table and Calendar table are joined by Completion Date.

Grateful for any assistance.

[Anonymous]

Hi @wmcintosh ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[wmcintosh]

@TheoC , @Anonymous 
Apologies for the delay and thanks for all responses, however I never did manage to solve my problem. 
I've provided some sample data below. 
I have measures for Actual RS in Month 1 ([Baseline]) and the delta for each subsequent month [MonthRR].
What I need is a measure for [Actual RS] for each subsequent month, the formula for which should be:
Actual RS for the previous month minus Month RR for the current month.

Any assistance greatly appreciated.

Date	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24
Actual RS	30,166	30,151	30,135	30,088	29,995	29,928	29,074	28,817	28,414	27,853	27,586	27,147	26,904	26,567	26,476	26,270	26,087	25,609	25,538	25,430	25,294	25,168	25,128	25,045	24,981	24,975	24,824	24,580	24,543
Month RR	-	15	16	47	93	67	854	257	403	206	267	439	243	337	91	206	183	478	71	108	136	126	40	83	64	6	151	244	37

[TheoC]

Hi @wmcintosh 

Are you wanting a measure to calculate the change or is this already calculated in your data source?  

If you already have the calculation, then just drag the Date on the X Axis of a chart, and the column with the Total Risk Score Minus column onto the Y Axis.

Alternatively, try the following:

Descending Running Total = 

CALCULATE (
    SUMX (
        'Risks (Project)' ,
        'Risks (Project)'[Pre Mitigation Risk Score] - 'Risks (Project)'[Risk Score Delta]
    ) ,
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ) ,
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ) ,
    ORDERBY ( 'Calendar'[Date] , DESC )
)

Thanks,

Theo

[wmcintosh]

@TheoC  Thanks for your reply. I do have a mesure that calculates the change ([Risk Score Delta]) however I need to be able to chart how each action reduces the overall risk score by the delta (starting from the sum of the pre-action scores) by action completion date. 

I tried the measure you suggested but this gave the error 'The ORDERBY function can only be used in the OrderBy parameter of windows functions'.

[TheoC]

Hi @wmcintosh 

Can you give the below a try (and apologies for the earlier error).

Descending Running Total = 

CALCULATE (
    SUMX (
        'Risks (Project)' ,
        'Risks (Project)'[Pre Mitigation Risk Score] - 'Risks (Project)'[Risk Score Delta]
    ) ,
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ) ,
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

[TheoC]

Hi @wmcintosh 

Can you please try this measure that I put forward:

Descending Running Total = 

CALCULATE (
    SUMX (
        'Risks (Project)' ,
        'Risks (Project)'[Pre Mitigation Risk Score] - 'Risks (Project)'[Risk Score Delta]
    ) ,
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ) ,
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Thanks heaps. 

Theo

[wmcintosh · Accepted solution]

Hi @TheoC 

That didn't work however I managed to get there with:

CALCULATE(
SUM('Risks (Project)'[Pre Mitigation Risk Score]),
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), ASC)
)
)

I was almost there originally - I think the issue was that some Action Completion Dates exceeded the range of my Date dimension table. Once I fixed that I got the desired visualisation.

Appreciate your responses throughout.

Thanks,
Wayne