Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Abevann
Helper III
Helper III

Total in bar graph and table is wrong

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).

Abevann_0-1652713738498.png

 

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.

Abevann_1-1652713738506.png

 

If I get a table with the daily sales, there is an incongruence with the results (left Power BI vs right excel):

Abevann_2-1652713738509.pngAbevann_3-1652713738510.png

 

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

1 ACCEPTED SOLUTION

Try this measure:

Previous Year =
SUMX (
    'Fecha table',
    CALCULATE ( [Sum Venta], DATEADD ( 'Fecha table'[Date], -1, YEAR ) )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
Abevann
Helper III
Helper III

OK, I'll do it and let you know, thanks

PaulDBrown
Community Champion
Community Champion

 Does the 'Fecha' table contain continuous dates covering the whole range of dates in the model?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Correct, Fecha is continuos and based in the table formula:  

Fecha = CALENDAR(MIN(Comercializar[DocDate]), MAX(Comercializar[DocDate]))
 
And choosing the "Show items with no data" option, it shows empty spaces for 1, 2 and so on and so forth...:
Abevann_0-1652720237207.png

 

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

Abevann_0-1652735534946.png

 

 

Abevann_1-1652735535000.png

 

 

Abevann_2-1652735535008.png

 

 

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:

Abevann_3-1652735535117.png

 

 

Can you share some sample data (non-confidential) please?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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.

Abevann_0-1652756502013.png

 

Try this measure:

Previous Year =
SUMX (
    'Fecha table',
    CALCULATE ( [Sum Venta], DATEADD ( 'Fecha table'[Date], -1, YEAR ) )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.