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,
I'm building a Power BI report which is analysing PoS transaction data so have a fact table (Transactions) with every individual transaction including timestamp and sales amount (Transactions[Sales]).
I've added a custom column for the transaction date (Transactions[Date]) which is joined to a calendar table (Calendar) which is generated from MIN(Transactions[Date]) to MAX(Transactions[Date]).
I've created measures for
Total Sales = SUM(Transactions[Sales] Total Sales Last Year = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))
The problem occurs when I include the last date in the Calendar table when calculating Total Sales Last Year.
As you can see below, if I select 13 - 14 July 2019 the calculations are correct i.e. the Total Sales Last Year are for the corresponding 2 days in 2018.
Note the the Date slider is Calendar[Date] and the Date column in the table is Transactions[Date].
If I change this to be 14 - 15 July 2019 where 15 July is the last date for any transactions, the Total Sales Last Year is incorrectly calculated for the period 14 - 30 July.
I have found a workaround which involves adding some additional days to the end of my Calendar table but the problem will occur again if these future days are selected in my date filter.
Am I using this function incorrectly or is there a bug?
Thanks,
Michael
Solved! Go to Solution.
Hi @michaelstone ,
Sorry for my mistake.
Please modify the measure Total Sales - Previous Year like below.
Total Sales - Previous Year = SUMX ( 'Calendar', CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) )
Here is the output.
Best Regards,
Cherry
Hi @michaelstone ,
Based on your information, I have made a test with your formula, I cannot reproduce your issue.
If it is convenient, could you share your data sample and so that we could help further on it?
You also could have a test on my test sample which has been attached below.
Best Regards,
Cherry
Hi @v-piga-msft ,
Here is a link to a file which shows the issue.
Note that the end date is now 17 July so you need to have this as the end date to see the issue.
Thanks,
Michael
Hi @michaelstone ,
By my test and research based on your pbix, I'm afraid that you should create the table with the date column from Calendar table like below so that you could get the correct output.
Best Regards,
Cherry
Hi @v-piga-msft ,
My production PBIX does use the Calendard[Date] column but as you can see from your example, the Total at the bottom of the Total Sales - Previous Year column is still incorrect.
I was only using Transactions[Date] in the visualisation because it showed the underlying problem i.e. that the SAMEPERIODLASTYEAR is not working correctly and is including dates to the end of the month.
Thanks,
Michael
Hi @michaelstone ,
Sorry for my mistake.
Please modify the measure Total Sales - Previous Year like below.
Total Sales - Previous Year = SUMX ( 'Calendar', CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) )
Here is the output.
Best Regards,
Cherry
This seems like a workaround for an underlying bug but will accept as the solution.
Thanks @v-piga-msft
Hi,
It is working fine wheni tried, if didnot set the Date columns with same format please do that.
Below is hte screenshot for same.
mark it correct, if its useful.
Thanks,
Surya Teja
Hi @suryaburaboyina ,
I don't understand what you mean by "did not set Date column with same format".
As per my instructions, the problem only occurs when you include the last day of data in the slicer i.e. 17 July 2019.
Thanks,
Michael
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |