Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I have attached a pbix file with a date table included. I am trying (and failing) to calculate a column that will let me know if the date is within the previous 13 months (but not including the current month). This sounds easy, but the reason I am struggling is because I need to count financial months and usually dates from one month fall into another financial month, as an example, 25th Oct 2020 - 31st Oct 2020 fall within the financial month of November 2020.
On the pbix file, there is a column ExpectedResults, this is the value I am trying to calculate in the IsRolling13M column
Please help
Solved! Go to Solution.
My bad, change the HelpColumn logic to this
HelpColumn = [FinancialYear]*12+[FinancialPeriod]
I managed to work it out, thank you to @Vera_33 for setting me on the right track.
It's a bit of a hack but the following code does the job
IsRolling13M =
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
RETURN
SWITCH(TRUE(),
// any month in the same financial year will be within 13 months
'Time'[FinancialYear] = _year && 'Time'[FinancialPeriod] < _month, TRUE(),
// any month in the previous year which is greater than or equal to the current month will also be within 13 months
'Time'[FinancialYear] = _year-1 && 'Time'[FinancialPeriod] >= _month, TRUE(),
// finally, if the current month is January we need to include December from 2 years previous
'Time'[FinancialYear] = _year-2 && _month = 1 && 'Time'[FinancialYear] = 12, TRUE(),
FALSE())
I managed to work it out, thank you to @Vera_33 for setting me on the right track.
It's a bit of a hack but the following code does the job
IsRolling13M =
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
RETURN
SWITCH(TRUE(),
// any month in the same financial year will be within 13 months
'Time'[FinancialYear] = _year && 'Time'[FinancialPeriod] < _month, TRUE(),
// any month in the previous year which is greater than or equal to the current month will also be within 13 months
'Time'[FinancialYear] = _year-1 && 'Time'[FinancialPeriod] >= _month, TRUE(),
// finally, if the current month is January we need to include December from 2 years previous
'Time'[FinancialYear] = _year-2 && _month = 1 && 'Time'[FinancialYear] = 12, TRUE(),
FALSE())
Hi @JimJim
In your Date table, all the FinancialYear, period...columns are already there? the only one is Rolling13M? Added a helper column, file attached. Let me know if you are looking for something else.
Thank you for your reply, but this doesn't work. November and December 2020 should be included in the Rolling 13 months but as you can see, your solution doesn't include any YEARS before the previous one
My bad, change the HelpColumn logic to this
HelpColumn = [FinancialYear]*12+[FinancialPeriod]
Thank you so much, this is much better than my solution
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |