Forum Discussion

Jarrod's avatar
Jarrod
Icon for Helper III rankHelper III
1 year ago
Solved

Cumulative Sales Measure Not Accounting for Missing Intervals

I am currently working on a report to track cumulative sales over 15-minute intervals, but I'm facing a challenge with my DAX measure. I need it to calculate the cumulative sales for selected products, even when some products have no sales data for certain intervals. Here's my current measure:

 

 

Cumulative Sales = 
VAR CurrentInterval = MAX(Fact_Sales[Min15Interval])
VAR SelectedProducts = VALUES(Fact_Sales[Product])
RETURN
    CALCULATE(
        SUM(Fact_Sales[SalesAmount]),
        ALL(Fact_Sales), // Remove filters on the entire table
        Fact_Sales[SalesType] = "Retail",
        Fact_Sales[Min15Interval] <= CurrentInterval,
        Fact_Sales[SalesShift] = MAX(Fact_Sales[SalesShift]), 
        // Ensure all selected products are included, even if they have missing intervals
        Fact_Sales[Product] IN VALUES(Fact_Sales[Product]) && Fact_Sales[Product] IN SelectedProducts
    )

 

 

Problem:

While this measure works to provide cumulative sales for intervals where sales data exists, it fails to account for selected products that may not have sales data for specific intervals. As a result, the cumulative totals can drop unexpectedly when multiple products are selected and some do not have data at specific intervals.

 

Sample file - 

https://drive.google.com/file/d/1ml7o6G2YYAyfkZapheCvbtCWKjvyiYkb/view?usp=drivesdk

.

Need Help

7 Replies

    • Jarrod's avatar
      Jarrod
      Icon for Helper III rankHelper III
      1 year ago

      Hi, I have a start schema.

       

      Fact_Sales

      Dim_ProductTypes

      Dim_Shifts

      Dim_Intervals

       

      If there is a better way to write this Dax, please advise.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        1 year ago

        Hi,

        Please share the link of your sample pbix file, and then I can try to look into it.

        Thank you.

  • PavanLalwani's avatar
    PavanLalwani
    Icon for Resolver II rankResolver II
    1 year ago

    The issue you're facing with **missing intervals** is because your current DAX measure is not accounting for those gaps in sales data. Here's an adjusted approach to fix it:

    ### Solution:
    1. **Create a disconnected table** for the intervals (a date/time table or specific intervals list).
    2. **Modify the measure** to use the disconnected table, ensuring all intervals are considered, even when no sales data exists for a product:

    ```DAX
    Cumulative Sales =
    VAR CurrentInterval = MAX(IntervalTable[Min15Interval])
    RETURN
    CALCULATE(
    SUM(Fact_Sales[SalesAmount]),
    FILTER(ALL(IntervalTable), IntervalTable[Min15Interval] <= CurrentInterval)
    )
    ```

    This ensures missing intervals still show cumulative values.