Forum Discussion
Need previous month flag
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 , 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))
1 Reply
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))