Disturbing: A summary report does not match the details using the same measure. Proved two ways

[Anonymous]

I give KUDOS and mark as solved! In this case, guesses are welcome!

I was auditing a report by downloading the daily details to make sure it was working right. 

It appears the summary is exchanging numbers between two part numbers (in this case). The aggregating Measure used is the same Measure. I even added the Date to the summary report to convert it to daily. That too then matched the detail daily dump. When removing the date the exchange takes place again. 

Why could this be? Guesses appreciated. 

The company is going to implement PBI and this is something that will make people reluctant to change to PBI to not trust it. I've never seen this before. 

Date Filter: 

Relative: In This Year

Measure: BI_Calendar[DatesWithSales] is just a marker for a date with a sale

The Measure: 

Quantity_PYTD = IF (

[DatesWithSales] ,

CALCULATE (

[QuantitySum],

CALCULATETABLE (

DATEADD ( BI_Calendar[Date], -1, YEAR ),

BI_Calendar[Date_Sales] = TRUE

) ) )

The outputs:

From PBI Report	From Excel using SUMIF() to add daily pulled details from the PBI Report
PartNumber	Quantity_PYTD	PartNumber	Quantity_PYTD	DIFF
467	1,161	410-12467	1,163	(2)
442	185	410-52442	185	-
389	242	410-52389	242	-
436	177	410-52436	175	2
	1,765		1,765

[Anonymous · Accepted solution]

Found the issue. Need Datawarehouse to fix it.

[v-yiruan-msft]

Hi @Anonymous ,

Please check whether apply any filter, slicer or RLS for the report in Power BI. And what's the date format in Power BI? For example, the date 2/3/2020 in excel be identified as the format with MM/DD/YYYY. But in Power BI, the date format may be DD/MM/YYYY, then that date will be Mar 2, 2020...

Best Regards

[Anonymous]

This is all in Power BI Desktop and all dates are from the PBI Calendar in the Cube.