Count number of changes to a date over last 3 months

[obriaincian]

Hi All,

Here's a sample of the data what I have. What I want to do is count the number of date changes to a SKU for the past 3 months only.

Date	SKU	Expiry Date
Dec-21	PPPT-01	25/01/2024
Dec-21	WEC-54	05/06/2023
Dec-21	ERRE-66	23/08/2025
Jan-22	PPPT-01	01/01/2024
Jan-22	WEC-54	05/06/2023
Jan-22	ERRE-66	23/08/2025
Feb-22	PPPT-01	01/01/2024
Feb-22	WEC-54	15/06/2023
Feb-22	ERRE-66	23/08/2025
Mar-22	PPPT-01	25/01/2024
Mar-22	WEC-54	15/06/2023
Mar-22	ERRE-66	23/08/2025

I'd like to output the results in a matrix that might look like the following:

SKU	Number of date changes
PPPT-01	1
WEC-54	2
ERRE-66	0

[amitchandak]

@obriaincian , try a measure like, Make sure you have date or create date using month year

Rolling 3 = CALCULATE(distinctcount(Table[Expiry Date]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

refer if needed

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

[obriaincian]

Thank you @amitchandak ,

Unfortunately this doesn't work with the Date being in mmm-yy format. It is returning the last 3 months but it is not taking the year into account. 

e.g. it is returning Jan 22 and Jan 21 when used on my actual dataset.

[obriaincian]

@amitchandak would you please be able to give me some advice on the above with regards to taking the year into account?

[V-lianl-msft]

Hi @obriaincian ,

Try to ceate a measure like below:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Date]),ALLEXCEPT('Table','Table'[SKU]),DATESINPERIOD('Table'[Date],TODAY(),-3,MONTH))

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

[obriaincian]

hi @V-lianl-msft thank you for your response. However it isn't a solution.

I may not have explained myself clearly. What I need to do is filter the "Date" column for all rows where date is within the last 3 months i.e. Jan-21, Feb-21,Mar-21.

Once I have that done I need to then check how many changes occured in the "Expiry Date" column for each SKU? 

Hope that makes sense?

[V-lianl-msft · Accepted solution]

Hi @obriaincian ,

Please modify the measure like below:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Date]),ALLEXCEPT('Table','Table'[SKU]),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-3,MONTH))-1

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

[obriaincian]

For some reason the tables above are not formatting correctly.