Total in bar graph and table is wrong

[Abevann]

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

[Abevann]

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

[PaulDBrown]

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

[Abevann]

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

[PaulDBrown]

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)

[Abevann]

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:

[PaulDBrown]

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

[Abevann]

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.

[PaulDBrown · Accepted solution]

Try this measure:

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

[Abevann]

Hey Paul,

Correct, I used your formula and worked thanks... anyway kind of weird , right?

Regards,

Abelardo

[Abevann]

I will and let you know, thanks

[Abevann]

Sure, I'll clean up the data and then send it, thanks