Need previous month flag

[Anonymous]

I want to add a column with previous 3 month flag in reverse order, when we go to the next month the month prior to it should be flaged as 1

Say we are in June now, I want to have "May" flaged as 1, "Apr" as 2

(when we are in Jan of 2023, Dec 2022 should be flagged as 1 and so on)

Year	Month	Flag
2021	November	0
2021	December	0
2022	January	0
2022	February	0
2022	March	3
2022	April	2
2022	May	1
2022	June

This is how my data looks like now

Category	Value	Fiscal Year	Fiscal Month	Date
Cate 1		2021	October	10-01-2020
Cate 1		2021	November	11-01-2020
Cate 1		2021	December	12-01-2020
Cate 1		2021	January	01-01-2021
Cate 1		2021	February	02-01-2021
Cate 1		2021	March	03-01-2021
Cate 1		2021	April	04-01-2021
Cate 1		2021	May	05-01-2021
Cate 1		2021	June	06-01-2021
Cate 1		2021	July	07-01-2021
Cate 1		2021	August	08-01-2021
Cate 1	25	2021	September	09-01-2021
Cate 1	25	2022	October	10-01-2021
Cate 1	25	2022	November	11-01-2021
Cate 1	27	2022	December	12-01-2021
Cate 1	27	2022	January	01-01-2022
Cate 1	27	2022	February	02-01-2022
Cate 1	29	2022	March	03-01-2022
Cate 1	29	2022	April	04-01-2022
Cate 2		2022	May	05-01-2022
Cate 3		2022	June	06-01-2022
Cate 4		2022	July	07-01-2022
Cate 5		2022	August	08-01-2022
Cate 6		2022	September	09-01-2022

Regards

Tanmay

[Anonymous · Accepted solution]

@Anonymous , create a Calculated Column with this DAX formula below. That will give you a field that holds the number of months between today's date and the [Date] field in each row.

Previous3MonthsFlag = DATEDIFF([Date], NOW(), MONTH) 

 Or if you want the measure to only have a value for the three months prior, then do this:

IF(DATEDIFF([Date], NOW(), MONTH) IN {1,2,3}, DATEDIFF([Date], NOW(), MONTH))