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 community.
I'm new in Power BI and I'm trying to make a simple period operation with a test data set. I have a time dimension table and a fact sales table.
I want to see the last year sales. I wrote this formula:
Total Sales = SUM(Sales[totalprice])
LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[date]))
The result show the totals, but blank for the details.
Is there a problem with the data? or the structure? or the formula? Here is the link to the pbix file and the source files:
https://www.dropbox.com/s/7qcczdu86r6ro6t/Archivos.zip?dl=0
Thanks and regards
Solved! Go to Solution.
OK the problem is that you're using a synthetic key to link the 2 tables. I was able to get it to work by replacing the sales date key in your sales table with a date and linking the date and sales tables by the dates.
This is likely because you're not using the date/year column from the date dimension table as the axis for your table. I wasn't able to download your .pbix file from your dropbox, but that would by my first guess.
I'm using the date dimension. The fact table only have ID's and Measures.
Can't you download the .zip file from dropbox? This is the link and is public:
OK the problem is that you're using a synthetic key to link the 2 tables. I was able to get it to work by replacing the sales date key in your sales table with a date and linking the date and sales tables by the dates.
Thanks cstaulbee. That work perfectly.
I have always used surrogate keys in my data marts and data warehouse models. I know now that Power BI requires a direct link with dates data types.
Thanks for the reply and regards
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |