Measure to verify status change between dates

grechill — Thu, 24 Oct 2019 22:21:52 GMT

Hello!

I'm trying to create a calcualted field to verify, if there was a Stage change between report date and previous report date based on opportunity number. Close date is not involved in this measure. Report dates are in the same column.

I have tryed many different options, but till now without any success :(

Data looks like this:

In written words it would be so: If between report from t and t-1 was a STAGE change, then give TRUE, else FALSE.

Main report is linked to second table, which defines stage order and some segmentation, and stage order can be used as a numerical value for calculated field.

I do not wish to merge tables, nor pivot the main table, as it would affect other report measures and whole structure.

What I would like to see in the end:

Or better 1 and 0 instead of True and False.

I attach a sample pbix.

Thanks a lot in advance!

Re: Measure to verify status change between dates

Nathaniel_C — Fri, 25 Oct 2019 03:12:14 GMT

Hi grechill ,

Here is my PBIX PBIX FILE , sorry didn't see yours. So, I would note that the True meets the criteria of a change between current date, and the next older date.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Change =
VAR _curDate = MyTable[Report Date]
VAR _curStage = MyTable[Stage]
VAR _calc =
    CALCULATE (
        IF ( MAX ( MyTable[Stage] ) <> _curStage, "True", "False" ),
        FILTER (
            ALLEXCEPT ( MyTable, MyTable[Opportunity Number] ),
            MyTable[Report Date] < _curDate
        )
    )
RETURN
    _calc

topic Re: Measure to verify status change between dates in DAX Commands and Tips

Measure to verify status change between dates

Re: Measure to verify status change between dates