DAX Measure to have cumulative sales

[Anonymous]

Hi,

May someone help me with a DAX measure to have cumulative sales for the month and this needs to pull everything I move my visual by month

Here is how my dataset is set up:

I have total sales for the currect year measure, calender date hierarchy 

The total sales measure here : 

Total Actual = CALCULATE(SUM('Sales Data file xlsx'[Volume (HL)]),

FILTER('Sales Data file xlsx',

'Sales Data file xlsx'[Key] = "IMSActual" ||

'Sales Data file xlsx'[Key] = "FOBActual"))

Please assist

[rohit1991 · Accepted solution]

Hi @Anonymous ,
To create a cumulative (running total) sales measure based on your existing Total Actual measure, and have it work as you move your visual by month (e.g., using the calendar hierarchy), you can define a new measure like this:

Cumulative Sales = 
CALCULATE(
    [Total Actual],
    FILTER(
        ALLSELECTED('Sales Data file xlsx'[Date]),
        'Sales Data file xlsx'[Date] <= MAX('Sales Data file xlsx'[Date])
    )
)

[v-csrikanth · Accepted solution]

Hi @Anonymous 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[rohit1991 · Accepted solution]

Hi @Anonymous ,
To create a cumulative (running total) sales measure based on your existing Total Actual measure, and have it work as you move your visual by month (e.g., using the calendar hierarchy), you can define a new measure like this:

Cumulative Sales = 
CALCULATE(
    [Total Actual],
    FILTER(
        ALLSELECTED('Sales Data file xlsx'[Date]),
        'Sales Data file xlsx'[Date] <= MAX('Sales Data file xlsx'[Date])
    )
)

[v-csrikanth]

Hi @Anonymous 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Anonymous 

Place your Cumulative Sales measure in a line chart.

Use 'Sales Data file xlsx'[Date] from the Date Hierarchy as the X-axis.

As you drill down from Month → Day, the cumulative sales should adjust accordingly.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[Anonymous]

Thank you this is showing me the correct value, now how do I have by day in my line chart

I have date hierarchy which has, Year,Quarter, Month and Day

[bhanu_gautam]

@Anonymous , Try using

Cumulative Sales MTD =
CALCULATE(
[Total Actual],
DATESMTD('Sales Data file xlsx'[Date])
)