Measure calculates cumulative sales

DAX DAX Calculations
1

I am asking for help in a measure that calculates cumulative sales

1
11106Ă—560 57.7 KB

2
21106Ă—560 61.4 KB

PBX: https://drive.google.com/open?id=1LskErVMWNBBZnqJlW7wCfZ7bXIkoTqZL

2

@Harris

When I make cumulative measures based on dates the DAX looks like the below, where Forecast USD Total is my sum measure and I have a dedicated Dates table.

Cumulative Forecast =
CALCULATE( [Forecast USD Total],
FILTER( ALLSELECTED( Dates ),
Dates[Date] <= MAX( Dates[Date] ) ) )

Your model has a dedicated Date table, but have you tried making another dedicated table for those 15-minute intervals you’ve called out to narrow down that context further?

3

@Harris,

The Measure shows that “Sales” is being filtered instead of dates.

Below is a simplified version with the small correction I made:

Cumulative_Sales =
CALCULATE ([SalesDaily],
    FILTER (ALLSELECTED ( Dim15mPeriods ),
        Dim15mPeriods[Dateto] <= MAX ( Sales[Dateto] )
    )
)

Below is your version that I made the adjustment to (pbix file was too large to upload):

Sales_Cumulative =
VAR LastSalesHour = CALCULATE ( MAX ( Sales[Dateto] ), ALL ( Sales ) )
RETURN
    IF (SELECTEDVALUE ( Sales[Dateto] ) > LastSalesHour,
        BLANK (),
        CALCULATE ([SalesDaily],
            FILTER (ALLSELECTED ( Dim15mPeriods ),
                Dim15mPeriods[Dateto] <= MAX ( Dim15mPeriods[Dateto] )
            )
        )
    )

2020-04-17_11-58-43
2020-04-17_11-58-431892Ă—892 123 KB

1 Like
4

Hi @Harris, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

5

Hi, powerbideveloper
Thank you for your help. Measure works perfectly.
We have also a second need.

If we have two lines in the same table, but we have more than 1 Store, we need another measure that will be a sum of cumulative sales for this “datefrom-dateto” period for a day, so it will be the same value in both rows
also, sum of cumulative sales_exceptSelected must be the same sum, but except value of Sales_Cumulative in current row.
:

Date / Weekday / StoreName / Datefrom / Dateto / Sales / Sales_Cumulative / Sum_Sales_Cumulative / Sum_Sales_Cumulative_ExceptSelected

01.04.2019 / Monday / Store1 / 11:15 / 11:30 / 68.10 / 71.90 / 351.90 / 280.00
01.04.2019 / Monday / Store2 / 11:15 / 11:30 / 100.00 / 200.00 / 351.90 / 151.90
01.04.2019 / Monday / Store3 / 11:15 / 11:30 / 50.00 / 80.00 / 351.90 / 271.90

how to create Sum_Sales_Cumulative and Sum_Sales_Cumulative_ExceptSelected measures?

6

@Harris

I’m glad it works perfectly.

This seem like a new question and you would be better served if you post it as a new question. This will open it up to more help and quicker response but I will definitely have a look at it when I have a moment.

7

Hi @Harris It’s great to know that you are making progress with your query. Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate. For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum