Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I've searched high and low for my question, I've seen bits and pieces that have helped me define it more but I don't think I've found exactly what I am looking for.
I have a data model that is a hub and spoke. The center is a sales transaction table with a date column, several measures (price, cost, etc.), and ID columns that link to Dimenesion Outline tables (product, channel, etc. hierachies). There is also a calendar table that has a date column, calendar year/quarter/month/week/day columns, and fiscal year/quarter/month/week/day columns (4-4-5 and not ending in Dec) , and I've thrown both of those calendars into hierachies. This works great if you're just looking at period totals for something like a line graph, but not so much where time intelligence is concerned.
What I'm going for is common time intelligence functions (*-to-date and trailing x periods) that will work for both calendar bases but won't require each measure (there are currently 20+) to be recreated.
A good end result, someone would coudl have a matrix/pivot table with time periods in the columns, a dimension outline on the rows, a few measures in the data, and easily flip the numbers between YTD, TTM, etc. Also the possiblity of filtering on a single time period and stringing the time intelligence across the rows.
I've seen this https://exceleratorbi.com.au/toggle-overlapping-time-periods-using-calendar-table/ but am not quite sure it is feasible to dynamically build a recursive table when I am keying on days or weeks across 8+ years. The numbers table is closing in on 1m rows so I want to be careful the file stays useable.
Thoughts on how this could be structured efficiently?
Solved! Go to Solution.
Calculation Groups are your friend, and you can create them in Power BI Desktop using Tabular Editor now 🙂
Check out https://www.sqlbi.com/calculation-groups/
You can create a calculation group that includes all Time Intelligence variants you require, and this is then appears as a table/column that can be filtered or placed on the axis of visuals.
Calculation Groups mitigate the very issue you described, where you would normally have an "explosion" of measures covering every variant of every base measure.
Please post back if you need more help.
Regards,
Owen
@Frank , not very clear. Refer if these can help
measure slicer
https://www.youtube.com/watch?v=vlnx7QUVYME
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
Calculation Groups are your friend, and you can create them in Power BI Desktop using Tabular Editor now 🙂
Check out https://www.sqlbi.com/calculation-groups/
You can create a calculation group that includes all Time Intelligence variants you require, and this is then appears as a table/column that can be filtered or placed on the axis of visuals.
Calculation Groups mitigate the very issue you described, where you would normally have an "explosion" of measures covering every variant of every base measure.
Please post back if you need more help.
Regards,
Owen
UPDATE: I now have this working in Excel. I put the data model into PowerBI, added calculation groups, and pulled down via Analyze in Excel which will now be the master template users pick up if they want to work in Excel. The calculation groups worked exactly as they did in PowerBI.
I'm finding a lot of other benefits too. After watching the SQLBI videos I'm using this to create additional time-based calculations I hadn't thought to include like CAGR and other growth measures. The Excel file updates faster and can be refreshed off prem. I noticed the MDX tools are availabe with this method too. I will have to do some one-time Excel updates on user's computers and have an extra layer of data refresh to keep up with but I think that's well worth it.
Thanks Owen!
@OwenAuger This solves the problem exactly in PowerBI. I need to apply it to Power Pivot in Excel also. Is there an add-in or other solution? I've been looking but search results are dominated by PowerBI.
I did find this blog post by the same author on Aug 1 where he has a Pivot Table with the Calculation Groups shown making a separate point. I'm not sure if that was just superficial for illustration purposes but that is where I need to get to.
https://www.sqlbi.com/blog/marco/2020/08/01/arbitrary-combination-of-measures-and-calculation-items/
I need to keep the same data model in Excel and PowerBI where the end user would not see a noticeable difference.
Thanks,
Frank
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
101 | |
94 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |