Forum Discussion
Unsamable value for Month / Quarter / Year
Hello all,
I have trouble finding how to construct my differents tables, and connect them my table view.
For each one of my asset, I have a month, quarter & yearly value. The quarter value are not equal to the sum of the 3 months value ; same goes for the year (the yearly value is not equal to the sum of the 4 quarters).
For now, I have one excel table made like this name "Extract Table" :
Period (ME / Q / Year) | ID | Year | Asset | Sales amount in $ |
| ME01 | Year - Period - Asset | 2023 | X1 | xx |
| Q1 | Year - Period - Asset | 2024 | ZB1 | xx |
| Yr24 | Year - Period - Asset | 2024 | ZB3 | xx |
I have thought to add a column date dd/mm/year, but I wouldn't know what to put for the line with a yearly value.
How do I transform my data so it can be able to recognize that 1 quarter has 3 months, 1 year 12 months ?
Even if i create one table for months value, one for quarter, one for year value, power bi seems unable to recognize the link between all three.
I have also tried to create a date table like this :
But Power BI doesn't recognize any link between the month / quarter or even year number from my "Extract Table" and the ones from the table set as a date table.
In the end, i need to be able to do mesure such as Sales for Previous Quarter ; Sales for Previous Year ; Sales for previous month ... for each asset & comparaison for each based on the year/quarter/month selected my filter.
Thank you for your help 😪
1 Reply
Wilson_Memorable Member
Hi mladird,
Keep your data at the month level and remove the quarterly or annual totals in the table. If you want to show sales for different time periods, use time intelligence functions instead. Here is a link to a comprehensive resource on time intelligence functions by SQLBI.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)