Power BI YTD Sales Calculation Issue with Appended Data Sources

[it_kwwk]

Hi there,

I have two data sources: a spreadsheet in SharePoint and the SAP database. I combined(Append) them into a single table in Power BI to display all data together and calculate the YTD sales amount.

I used two filters, 'Year' and 'Month', from the appended table to control the data display.

The spreadsheet contains data from July 2024 to August 2024, while the SAP database holds data from September 2024 to March 2025.

When I set the filters to 2024 and August, the YTD sales amount is correct, summing the values from July to August 2024 from both spreadsheet and SAP. However, when I change the filter to 2024 and September, the YTD sales amount is incorrect—it only calculates values from the SAP database, and the spreadsheet data does not appear in the table.

Can someone help this? Thanks a lot.

[v-karpurapud]

Hi @it_kwwk 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @it_kwwk 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @it_kwwk 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud · Accepted solution]

Hi @it_kwwk 

Thank you for reaching out to the Microsoft Fabric Community Forum.

To ensure accurate YTD calculations across all data sources regardless of date presence, implement a separate Date Table and use it to control the filters and time intelligence functions.

1. Create a calculated table with a full range of dates covering both sources:

     DateTable =

     ADDCOLUMNS (

    CALENDAR (DATE(2024, 1, 1), DATE(2025, 12, 31)),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMMM"),

    "MonthNumber", MONTH([Date]),

    "YearMonth", FORMAT([Date], "YYYY-MM")

     )

2. Mark as Date Table using the [Date] column.

3. Create Relationship between DateTable[Date] to AppendedTable[Date].

4. Update Slicers by using Year and Month from the Date Table, not from the appended data.
5. Create YTD Sales Measure by using the below DAX
 

     CurrentYTD_Sales =

     TOTALYTD(

    SUM(AppendedSales[SalesAmount]),

    DateTable[Date]

     )

6. Use this measure in your matrix or visuals. It will now calculate YTD correctly, even when data is missing for a specific month in one source.

I have attached the PBIX file for you reference . Please go through it .

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Additionally, thank you @Ritaf1983 for your quick response.

Thank You
 

[Ritaf1983]

Hi @it_kwwk 

 If the data isn’t displaying continuously, the append didn’t work properly.

Here are a few things to check:

Data Types – Ensure that the fields from Excel and SAP have matching data types, especially those used for filtering and appending.

Field Structure – If you're using separate Year and Month fields instead of a full Date field, check if they are stored as text. If so:

Make sure there are no hidden spaces (TRIM in Power Query can help).
Check for mismatches between sources (e.g., Year stored as a number in one and text in another).
Verify the appended table – Load the combined data without filters and see if everything is there. This will help determine whether data is missing due to the append process or filtering.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly