SUM function incorrectly calculates weekly sales for Non Standard Date calendar

[Dhwanil]

Posting this again as old question got spammed some how!
Hello Experts,

I am working on a excel report, where i get monthly data (at the weekly level). Combining these monthly files has been automated using power query functionalities.

I am trying to create YTD sales, L4W, L12W and L52W. As a starting point, I created simple sales measure,

i.e TY_Sales = SUM(facttble[Sales])

I have a custom calendar as below.

I have recently uploaded Dec'24 and Jan'25 data. 

Somehow weekly sales for Dec'24 and 1st week of Jan'25 are doubled when I use the standard sales measure (TY_Sales = SUM(facttble[Sales]). Even when I created a pivot table using facttable (using dates from facttable but not date calendar), it shows doubled sales.

			raw field from pivot	measure
Year	Month	Week	Sum of  Sales	TY_1P_Sales	Correct sales
2024	Dec	49	84312.96	£84,313.0	£42,156.5
2024	Dec	50	83510.24	£83,510.2	£41,755.1
2024	Dec	51	87866.06	£87,866.1	£43,933.0
2024	Dec	52	65156.12	£65,156.1	£32,578.1
2025	Jan	1	77637	£77,637.0	£38,818.5
2025	Jan	2	41309.91	£41,309.9
2025	Jan	3	41520.1	£41,520.1
2025	Jan	4	42653.63	£42,653.6
2025	Jan	5	45909.54	£45,909.5

Below are the queries i have set up for this report:

Where the first 2 are the folder paths, Importing_Historical_Data (older files from 2022), and Importing_TYData (files for 2024 onwards). FactTables is a created using = Table.Combine({Importing_TYData, Importing_Historical_Data}).

Model is properly linked;

I am unable to figure out the issue/reason why few weeks' sales are doubled! Could anyone please help me out with the possible reason and a solution?

Thanks in Advance!

[ZhangKun]

If I'm guessing correctly, this problem is because of filtering.

When we filter a worksheet (as long as you turn on filtering once), a hidden name is created in the workbook (or file) (you can't see it in the Name Manager of Excel). Unfortunately, it can be read by the Excel.Workbook function in Power Query.

If you use a connector to read Excel files, the connector will automatically filter out hidden names. But if it is user-written code, it may ignore this feature, and all you need to do is filter the Hidden column.

[Dhwanil]

Hi @ZhangKun ,

Thanks for your response. It's an interesting one!

does it happen even when there is only one sheet and no need to filter?

[ZhangKun]

As long as filtering is turned on and the file is saved, a hidden name is created. Even if you turn filtering on, then turn it off, and then save it, it's the same.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[v-aatheeque · Accepted solution]

Hi @Dhwanil ,
Thanks for reaching out to Microsoft Fabric Community Forum.
Based on the provided screenshot we understood that there might be duplicate records in the results set.
To get the distinct rows apply the below transformation :

let
CombinedTable = Table.Combine({Importing_TYData, Importing_Historical_Data}),
FactTable= Table.Distinct(CombinedTable)
in
FactTable

Post creating the table, create a same measure and check the data. This might be helpful.
If the issue still persists as per the @lbendlin  suggestion please share the sample data.

 If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!
 

[Dhwanil]

Thanks @v-aatheeque , I did check all the excle files used in this report but there are no duplicated rows. But still applying Table.Distinct(CombinedTable) helped to resolve the issue. It is still a mystery why rows for only one specifc months get doubled.