Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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))
@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))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |