Forum Discussion

it_kwwk's avatar
it_kwwk
Frequent Visitor
1 year ago
Solved

Power BI YTD Sales Calculation Issue with Appended Data Sources

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 ...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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's avatar
Ritaf1983
Icon for Super User rankSuper User
1 year ago

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