Forum Discussion
SUM function incorrectly calculates weekly sales for Non Standard Date calendar
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!
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!
6 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
v-aatheequeCommunity Support
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!
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.
ZhangKunIf 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.
- 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.
