Calculated Column - Return Previous Score based on Multiple Column Criteria

[Anonymous]

Hi,

I have survey results across multiple Clients and Criteria. These surveys take place at different Cycles during the year. I want to be able to work out the difference in score between each Cycle for each Client and Criteria. I initially did this using measures, which all worked fine. But there's a requirement to have a Legend on the report identifying where results have Increased or Decreased. As I'm not able to put a Measure into a Legend I need to create Calculated Columns to get to my answer.

If I use the following DAX to get the Client and Critieria scores for each Cycle:

Score - Average by Criteria = CALCULATE([Score - Average, Overall],ALLEXCEPT('Pulse - Results','Pulse - Results'[Client],'Pulse - Results'[Criteria],'Pulse - Results'[Cycle]))

What I'd like to do it work out a Calculated Column that returns the score for each Client and Criteria for the previous Cycle - these are number values.

Any ideas on how I do this? I was thinking I would use EARLIER but I'm not sure how to apply this.

Thanks,

MarkJames

[OwenAuger · Accepted solution]

Hi @Anonymous 

If you just want to modify the code you posted to filter on the previous cycle (assuming it is 1 less than current cycle), then here are a few different expressions that should work in a calculated column. The only difference is the way the Cycle filter is created:

Score - Average by Criteria (previous cycle) =
CALCULATE (
    [Score - Average, Overall],
    ALLEXCEPT (
        'Pulse - Results',
        'Pulse - Results'[Client],
        'Pulse - Results'[Criteria]
    ),
    TREATAS ( { 'Pulse - Results'[Cycle] - 1 }, 'Pulse - Results'[Cycle] )
)

Score - Average by Criteria (previous cycle) =
CALCULATE ( 
    [Score - Average, Overall],
    ALLEXCEPT (
        'Pulse - Results',
        'Pulse - Results'[Client],
        'Pulse - Results'[Criteria]
    ),
   'Pulse - Results'[Cycle] = EARLIER ( 'Pulse - Results'[Cycle] ) - 1
)

Score - Average by Criteria (previous cycle) =
VAR CurrentCycle = 'Pulse - Results'[Cycle]
RETURN
    CALCULATE (
        [Score - Average, Overall],
        ALLEXCEPT (
            'Pulse - Results',
            'Pulse - Results'[Client],
            'Pulse - Results'[Criteria]
        ),
        'Pulse - Results'[Cycle] = CurrentCycle - 1
    )

Regards,

Owen

[Anonymous]

Thanks @OwenAuger , this has worked an absolute treat.