Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Guys,
I have an issue with one customer. The problem is in a graph; I have 3 columns with sales from previous year, Sales and Sales target. Everything seems to be fine when I configure it up the previoud date (here is My 12th).
The issue is when I configure the date slicer to the last date (in this case May 13th), the value for previous year sales jumps from the day’s one $1,318,161.32 (May 12th) to the monthly one $5,564,885.04. In the table is possible to see that the sales for 13th are for $413,335.14, so the result is not correct. The bar is the month’s total instead of the daily’s one.
If I get a table with the daily sales, there is an incongruence with the results (left Power BI vs right excel):
The sum is $5,564,885.04 vs $1,731,496.46 (correct one). As a reference, this is the DAX I’m using to calculate the previous year sales:
_Ventas año anterior = CALCULATE(SUM(Comercializar[Venta]), DATEADD(Fecha[Fecha], -1, YEAR))
It seems there is a bug, right? Any suggestion to solve it?
Thanks a lot, regards,
Abelardo
Solved! Go to Solution.
Try this measure:
Previous Year =
SUMX (
'Fecha table',
CALCULATE ( [Sum Venta], DATEADD ( 'Fecha table'[Date], -1, YEAR ) )
)
Proud to be a Super User!
Paul on Linkedin.
OK, I'll do it and let you know, thanks
Does the 'Fecha' table contain continuous dates covering the whole range of dates in the model?
Proud to be a Super User!
Paul on Linkedin.
Correct, Fecha is continuos and based in the table formula:
It looks like you are using fields from an automatic date hierarchy. You should be using the fields from the Fecha table (which btw you should mark as the date table if you haven't)
Proud to be a Super User!
Paul on Linkedin.
Hey Paul,
I marked as data table and lost the hierarchy; then I did my own using Fecha as a hierarchy “root”, and manually add year, month, and day, but the results were exact same thing; in the daily view, the day shows the correct amount, and same thing than the table but once I build my own hierarchy and show monthly results, I got the wrong result.
Besides that, even if I don’t use hierarchy, only “Fecha” (marked as date Table), got same wrong result $5,564,885.04 instead of $1,731,496.46:
Can you share some sample data (non-confidential) please?
Proud to be a Super User!
Paul on Linkedin.
There you go, thanks!
https://1drv.ms/x/s!AlLivjsApnMZhK5AhdooEGIAMAMZlg?e=PlWztb
With this data, I have this simple GUI, with same results, the sum is $5,564,885.04; if I filter up to May 12, the sum is correct.
Try this measure:
Previous Year =
SUMX (
'Fecha table',
CALCULATE ( [Sum Venta], DATEADD ( 'Fecha table'[Date], -1, YEAR ) )
)
Proud to be a Super User!
Paul on Linkedin.
Hey Paul,
Correct, I used your formula and worked thanks... anyway kind of weird , right?
Regards,
Abelardo
I will and let you know, thanks
Sure, I'll clean up the data and then send it, thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |