Sum of cumulative measure

DAX DAX Calculations
1

Hi,

We got (thanks to @powerbideveloper a measure which calculates cumulative sales in table like below

pic

The measure is:
    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] )
                )
            )
        )

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?

2

Hi @Harris, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @Harris

Please share a sample PBIX file for us to look into this.

Thanks
Ankit J

4

Hi @Harris, we’ve noticed that no response has been received from you since the 30th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. 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 checkbox. Thanks!

5

I will shorten the discription of what we need. A measure that will be a sum of cumulative sales for selected:
Store
WeekDay
datefrom-dateto

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

01.04.2019 / Monday / Store1 / 11:15 / 11:30 / 68.10 / 71.90 / 351.90
08.04.2019 / Monday / Store1 / 11:15 / 11:30 / 100.00 / 200.00 / 351.90
15.04.2019 / Monday / Store1 / 11:15 / 11:30 / 50.00 / 80.00 / 351.90

how to create Sum_Sales_Cumulative measure?

I also made a solution in attached excel file, on sample data. Sum_Sales_Cumulative is in orange column. Maybe it will help.

Sample PBX and xlsx

6

Hi @Harris

Can you try this measure

Sum_Sales_Cumulative = 
CALCULATE ([Sales_Cumulative], ALLSELECTED ( DimDate ) )

Thanks
Ankit J

7

Thank you @ankit
This works as long as we select to see all rows in visual.
But if we’d like to filter f.e. only 01.04.2019, We still want to see proper value in this measure. Can You help us with that?

On the other hand, if we do this:

Sum_Sales_Cumulative = CALCULATE([Sales_Cumulative];ALL(DimDate))

We are close but will get sum for all Weekdays, we want this sum only for selected WeekDay (f.e. Monday)

8

Hi @Harris

Try this : 
    Sum_Sales_Cumulative =
    CALCULATE([Sales_Cumulative],ALL(DimDate[DateName]))

Thanks
Ankit J