topic Re: Count number of changes to a date over last 3 months in Desktop

Count number of changes to a date over last 3 months

obriaincian — Tue, 08 Mar 2022 10:07:55 GMT

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

Re: Count number of changes to a date over last 3 months

obriaincian — Tue, 08 Mar 2022 10:08:38 GMT

For some reason the tables above are not formatting correctly.

Re: Count number of changes to a date over last 3 months

amitchandak — Tue, 08 Mar 2022 10:12:52 GMT

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

Re: Count number of changes to a date over last 3 months

obriaincian — Tue, 08 Mar 2022 15:20:43 GMT

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.

Re: Count number of changes to a date over last 3 months

obriaincian — Wed, 09 Mar 2022 15:01:42 GMT

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

Re: Count number of changes to a date over last 3 months

V-lianl-msft — Fri, 11 Mar 2022 07:35:57 GMT

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.

Re: Count number of changes to a date over last 3 months

obriaincian — Fri, 11 Mar 2022 10:17:01 GMT

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?

Re: Count number of changes to a date over last 3 months

V-lianl-msft — Tue, 22 Mar 2022 08:21:31 GMT

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.