Forum Discussion
Custom Fiscal Calendar Time Intelligence Measures
Hello Power BI Community,
We are in the process of trying to create the following time-intelligence measures using Fiscal Year, Fiscal Month & Fiscal Week fields.
- Prior Month Weekly Average: Calculate [SelectedMeasure()]] for the prior fiscal month/number of weeks in the prior fiscal month
- 3 Month Weekly Average: Calculate [SelectedMeasure()] for the prior 3 fiscal months/number of weeks in the prior 3 fiscal months
- Prior Year Weekly Average: Calculate [SelectedMeasure()] for the prior fiscal year/number of weeks in the prior fiscal year
This is the current code that we are using to try to calculate the 3 Month Fiscal Average, but we need to swap out the filters that are referencing the date key for filters that reference the fiscal date parts.
The below screenshot shows the period ranges that need to be included for each caclculation. The 'Invoice Revenue Total' is a calculated measure in our data model and not a calculated column. The fact tables in our model are at a daily level of granularity.
We are not able to use the out-of-the-box time intelligence calculations because we dont have an actual 'Fiscal Date' field and field with a 'Date' datatype is required for using those measures. We are stuggling to find the correct DAX code to achieve these averages. Please help! Here is a sample data table for testing.
| End of Week | Fiscal Year | Fiscal Month | Fiscal Week | Fiscal Week Name | Invoice Revenue Total |
| 1/9/2021 | 2021 | Jan-21 | 1 | FY21 WK1 | $1,000.00 |
| 1/16/2021 | 2021 | Jan-21 | 2 | FY21 WK2 | $1,232.00 |
| 1/23/2021 | 2021 | Jan-21 | 3 | FY21 WK3 | $1,232.00 |
| 1/30/2021 | 2021 | Jan-21 | 4 | FY21 WK4 | $1,234.00 |
| 2/6/2021 | 2021 | Feb-21 | 5 | FY21 WK5 | $4,321.00 |
| 2/13/2021 | 2021 | Feb-21 | 6 | FY21 WK6 | $2,243.00 |
| 2/20/2021 | 2021 | Feb-21 | 7 | FY21 WK7 | $3,324.00 |
| 2/27/2021 | 2021 | Feb-21 | 8 | FY21 WK8 | $3,324.00 |
| 3/6/2021 | 2021 | Mar-21 | 9 | FY21 WK9 | $4,433.00 |
| 3/13/2021 | 2021 | Mar-21 | 10 | FY21 WK10 | $4,582.64 |
| 3/20/2021 | 2021 | Mar-21 | 11 | FY21 WK11 | $5,002.66 |
| 3/27/2021 | 2021 | Mar-21 | 12 | FY21 WK12 | $5,422.67 |
| 4/3/2021 | 2021 | Mar-21 | 13 | FY21 WK13 | $5,842.69 |
| 4/10/2021 | 2021 | Apr-21 | 14 | FY21 WK14 | $6,262.71 |
| 4/17/2021 | 2021 | Apr-21 | 15 | FY21 WK15 | $6,682.72 |
| 4/24/2021 | 2021 | Apr-21 | 16 | FY21 WK16 | $7,102.74 |
| 5/1/2021 | 2021 | Apr-21 | 17 | FY21 WK17 | $7,522.76 |
| 5/8/2021 | 2021 | May-21 | 18 | FY21 WK18 | $7,942.77 |
| 5/15/2021 | 2021 | May-21 | 19 | FY21 WK19 | $8,362.79 |
| 5/22/2021 | 2021 | May-21 | 20 | FY21 WK20 | $8,782.81 |
| 5/29/2021 | 2021 | May-21 | 21 | FY21 WK21 | $9,202.82 |
| 6/5/2021 | 2021 | Jun-21 | 22 | FY21 WK22 | $9,622.84 |
| 6/12/2021 | 2021 | Jun-21 | 23 | FY21 WK23 | $10,042.86 |
| 6/19/2021 | 2021 | Jun-21 | 24 | FY21 WK24 | $10,462.87 |
| 6/26/2021 | 2021 | Jun-21 | 25 | FY21 WK25 | $10,882.89 |
| 7/3/2021 | 2021 | Jun-21 | 26 | FY21 WK26 | $11,302.91 |
| 7/10/2021 | 2021 | Jul-21 | 27 | FY21 WK27 | $11,722.92 |
| 7/17/2021 | 2021 | Jul-21 | 28 | FY21 WK28 | $12,142.94 |
| 7/24/2021 | 2021 | Jul-21 | 29 | FY21 WK29 | $12,562.96 |
| 7/31/2021 | 2021 | Jul-21 | 30 | FY21 WK30 | $12,982.97 |
| 8/7/2021 | 2021 | Aug-21 | 31 | FY21 WK31 | $13,402.99 |
| 8/14/2021 | 2021 | Aug-21 | 32 | FY21 WK32 | $13,823.01 |
| 8/21/2021 | 2021 | Aug-21 | 33 | FY21 WK33 | $14,243.02 |
| 8/28/2021 | 2021 | Aug-21 | 34 | FY21 WK34 | $14,663.04 |
| 9/4/2021 | 2021 | Sep-21 | 35 | FY21 WK35 | $15,083.06 |
| 9/11/2021 | 2021 | Sep-21 | 36 | FY21 WK36 | $15,503.07 |
| 9/18/2021 | 2021 | Sep-21 | 37 | FY21 WK37 | $15,923.09 |
| 9/25/2021 | 2021 | Sep-21 | 38 | FY21 WK38 | $16,343.11 |
| 10/2/2021 | 2021 | Sep-21 | 39 | FY21 WK39 | $16,763.12 |
| 10/9/2021 | 2021 | Oct-21 | 40 | FY21 WK40 | $17,183.14 |
| 10/16/2021 | 2021 | Oct-21 | 41 | FY21 WK41 | $17,603.16 |
| 10/23/2021 | 2021 | Oct-21 | 42 | FY21 WK42 | $18,023.17 |
| 10/30/2021 | 2021 | Oct-21 | 43 | FY21 WK43 | $18,443.19 |
| 11/6/2021 | 2021 | Nov-21 | 44 | FY21 WK44 | $18,863.21 |
| 11/13/2021 | 2021 | Nov-21 | 45 | FY21 WK45 | $19,283.22 |
| 11/20/2021 | 2021 | Nov-21 | 46 | FY21 WK46 | $19,703.24 |
| 11/27/2021 | 2021 | Nov-21 | 47 | FY21 WK47 | $20,123.26 |
| 12/4/2021 | 2021 | Dec-21 | 48 | FY21 WK48 | $20,543.27 |
| 12/11/2021 | 2021 | Dec-21 | 49 | FY21 WK49 | $20,963.29 |
| 12/18/2021 | 2021 | Dec-21 | 50 | FY21 WK50 | $21,383.31 |
| 12/25/2021 | 2021 | Dec-21 | 51 | FY21 WK51 | $21,803.32 |
| 1/1/2022 | 2021 | Dec-21 | 52 | FY21 WK52 | $22,223.34 |
| 1/8/2022 | 2022 | Jan-22 | 1 | FY22 WK1 | $22,643.36 |
| 1/15/2022 | 2022 | Jan-22 | 2 | FY22 WK2 | $23,063.37 |
| 1/22/2022 | 2022 | Jan-22 | 3 | FY22 WK3 | $23,483.39 |
| 1/29/2022 | 2022 | Jan-22 | 4 | FY22 WK4 | $23,903.41 |
| 2/5/2022 | 2022 | Feb-22 | 5 | FY22 WK5 | $24,323.42 |
| 2/12/2022 | 2022 | Feb-22 | 6 | FY22 WK6 | $24,743.44 |
| 12/31/2022 | 2022 | Dec-22 | 52 | FY22 WK52 | $25,163.46 |
Thank you.
1 Reply
"Prior Month Weekly Average"
That screaming sound you hear is every developer who ever tried to explain to management that weeks and months are incompatible.
You must have a calendar table. It must be contiguous. It is highly recommended that you maintain it outside of Power BI. If you attempt to implement your fiscal year "logic" in Power BI you will fail. Miserably.
Once you have a calendar table in place with the requisite flags (week number, quarter number, fiscal year number for each date) you can then write your above measures. Be always very clear that the measures you are asked to write will be at best misleading and at worst useless.
