How to calculate the number of time a date has changed for a certain record

[iKettle]

Hi Guys, I need some help and the steps I would need to follow to get this calculation.

I have data that looks like this 

trackerid	enddate
120	2025/09/30 00:00
157	2025/07/31 00:00
157	2025/08/31 00:00
165	2025/07/31 00:00
167	2025/07/31 00:00
168	2025/07/31 00:00
169	2025/04/30 00:00
171	2025/07/31 00:00
172	2025/07/25 00:00
172	2025/08/31 00:00
175	2025/07/18 00:00
176	2025/09/11 00:00
178	2025/04/30 00:00
178	2025/08/31 00:00

What I want to do is to calculate the number of time the enddate has changes for that specific trackerid. Can anyone help?

[danextian]

Hi @iKettle 

You did not provide the expected result but assuming the current date <> to the previous date for the specific tracker id is a change, try this:

check = 
VAR currentDate = 'Table'[enddate]
VAR trackerID = 'Table'[trackerid]
VAR prevDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[trackerid] = trackerID
                && 'Table'[enddate] < currentDate
        ),
        [enddate]
    )
RETURN
    IF ( NOT ( ISBLANK ( prevDate ) ) && currentDate <> prevDate, 1 )

This also assumes that if there's no date before current, it isn't a change.

[iKettle]

Thanks. But where do I place this formula in - as a new measure or in dax - I just need that explained. 

[FBergamaschi]

You can create a measure

Numero of variations=

DISTINCTCOUNT (Table[end date]) -1

Then put the track id in rows and the above measure in values

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[iKettle]

Thank you, I tried that. So I added a new measure and used this formula;

DISTINCTCOUNT (Table[end date]) -1

But it gives me 0, even though I should have at least 1 for some of the ID.....

[FBergamaschi]

Can you show an image?

[iKettle]

[FBergamaschi · Accepted solution]

Remove the end date from the visual, otherwise you get zero because every couple ID and date has only 1 value (- 1 = 0)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI