Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎03-11-2018
Accepted Solution

Sum of measure is incorrect

So... i just started using power BI on Friday and everything has been going great until now.. this problem has been stumping me for at least 8hrs and I havent found anything that works on this forum or any other. 

 

So background... I watched a video yesterday on how to use the dateadd function to allow me to compare the data from two distinct time periods.

 

 

Cans Produced LY = CALCULATE(SUMX('Production Data','Production Data'[Cans Produced]), DATEADD('Production Data'[Date],-1,Year))

 

And with some tweaking i got the result I wanted and i was able to generate a time series plot comparing production from one date range slice to the same date range slice from the previous year:

 

Capture 1.PNG

So next I wanted to add two simple cards to my report summarising the total production in this year and last year for the date slice and this is what I got:

Capture 2.PNG

ok. and this is where I get lost.. so my total for this years production is correct but the total for last years production is wayyyy off. 

 

I changed the date slicer to a three day window to see if that made a difference but got the same problem, i created a report table to see what data i was working with and got this:

Capture 3.PNG 

The data in the table is correct but the sum for the cans produced LY is not. For the life of me I cant seem to figure out why.

 

I hope someone can help me with this. Thanks for taking to read and please comment back if you know what I'm screwing up.

 

Here are a few more snips from the file for information:

 

 

Capture 4.PNG

Capture 5.PNGCapture 6.PNG

 

 

 

 


Accepted Solutions
Established Member
Posts: 228
Registered: ‎03-22-2017

Re: Sum of measure is incorrect

Try using the date dim table instead of the production date on time intelligence functions.

View solution in original post


All Replies
Established Member
Posts: 228
Registered: ‎03-22-2017

Re: Sum of measure is incorrect

Try using the date dim table instead of the production date on time intelligence functions.

Frequent Visitor
Posts: 4
Registered: ‎03-11-2018

Re: Sum of measure is incorrect

Thats the problem, the other calendar does not have contiguous dates! Thanks Paul! 

Established Member
Posts: 228
Registered: ‎03-22-2017

Re: Sum of measure is incorrect

[ Edited ]

Happy to help! BTW, I'm not sure you need the SUMX function in your measure. Try with a simple SUM.

 

Regards,

Paul.