Data getting filtered differently on Power Pivot and M Query

[Anonymous]

Hi,

The table structure is as attached in the snapshot.

Problem that i m facing is, some date values do not match the entries in the US_2 table when the date table is designed from Power Pivot. there is no such issue when Date Table is designed from M Query editor.

(I used the same file to check this scenario)

* I do not see error on date column in US_2

* Date range in the calendar table designed from Power Pivot a/ Power Query is the same

* Data type and format verified in both US_2 and DIM_DateTable and are found to be consistent

However, I see that there are some date values (in US_2) that do not match with date values in calendar table (when the table is built using Power Pivot). There is no such problem when I use M Query editor.

Can anyone suggest what could be the issue?

Date range for Calendar table is picked up from helper table which hold relative start and end dates

Data of Usage Statistics

c					11-02-2017	11-03-2017	11-06-2017	11-07-2017	11-08-2017	11/14/2017	11/15/2017	11/16/2017	11/17/2017	11/20/2017	11/21/2017	11/22/2017	11/25/2017	11/27/2017	11/28/2017	11/29/2017	12-01-2017	12-04-2017	12-05-2017	12-06-2017	12-07-2017	12-08-2017	12-11-2017	12-12-2017	12/13/2017	12/14/2017	12/15/2017	12/18/2017	12/19/2017	12/20/2017	12/21/2017	12/28/2017	01-02-2018	01-03-2018	01-04-2018	01-05-2018	01-08-2018	01-09-2018	01-10-2018	01-11-2018	01-12-2018	1/15/2018	1/16/2018	1/17/2018	1/18/2018	1/19/2018	1/22/2018
0	Portal Report Name	Query Description	User Name	Function	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time	Total time
1	Top N to be Collected PRMS	Top N to be Collected PRMS	A	CM	5	22		22					22				22			22				22						22				222			222									11			22
2	Top N to be Collected PRMS	Top N to be Collected PRMS	B	CM		55																333			33		333						33				333		333				33				22			33	11
3	Top N to be Collected PRMS	Top N to be Collected PRMS	C	CM			555			22		44			44				44				44	44				44		44		44		44								44		44					44
4	Top N to be Collected PRMS	Top N to be Collected PRMS	D	Collection Team				11																													44			44								55
5	Top N to be Collected PRMS	Top N to be Collected PRMS	E	Collection Team					11		55			55				55				55					55				55		22		33			55	22		55		11		55		111			55
6	Top N to be Collected PRMS	Top N to be Collected PRMS	F	Atr					11
7	Top N to be Collected PRMS	Top N to be Collected PRMS	G	CM						22		22													33			22
8	Top N to be Collected PRMS	Top N to be Collected PRMS	H	CM			33		33					44				44		33			22
9	Top N to be Collected PRMS Per CLSP	Top N to be Collected PRMS Per CLSP	I	CM

US_1 comprises of Row_Id, Username, Function Name and Report name

US_2 comprises of Row_Id, date columns (Unpivot)

Date column from DIM_DateTable connects to Date field (Attribute) in US_2

There exists relationship between US_1 and US_2 on Row ID field.

Correct Snapshot:

Error Snapshot

[v-qiuyu-msft]

Hi @Anonymous,

Would you please share a pbix file to clarify the issue? 

Best Regards,
Qiuyun Yu