New Enterprise DNA Initiatives

Calculating cumulative sales last quarter

Hello,

I’m trying to calculate cumulative sales and compare it to cumulative sales last quarter. My cumulative sales measure is working, however when I plug that measure into the cumulative sales LQ, and use date add to change the context to last quarter, it is not adding up properly.

Here is my cumulative sales measure:

Cumulative Sales = CALCULATE([Total Sales],
FILTER(ALLSELECTED(‘Date’),
‘Date’[Date] <= MAX(‘Date’[Date]) ) )

Here is my cumulative sales last quarter measure:

Cumulative Sales LQ = CALCULATE([Cumulative Sales],
DATEADD(‘Date’[Date], -1, QUARTER) )

Here is a snapshot of how these measures are displaying on a table:

cumulative sales LQ

My cumulative sales LQ measure is simply spitting out total sales from last quarter, but it is not taking a cumulative total. I’m not sure why this is happening, since I’m measure branching with the working cumulative sales measure.

Thanks for your help!

Pete,

Your measure is actually doing exactly what it’s being asked to, which is to return the total cumulative sales for the last quarter. I think instead what you want is to return cumulative sales for all selected dates through the last quarter :

image

Give this a go:

Cumulative Sales Through LQ = 

VAR CurrentQ =
CALCULATE(
    MAX( Dates[Quarter & Year] ),
    Dates[Date] = TODAY()
)

VAR Result =
CALCULATE(
    [Cumulative Sales],
    Dates[Quarter & Year] < CurrentQ
)

RETURN 
Result

I hope this is helpful. Full solution file attached below.

Thanks, Brian.

I plugged your DAX into my model as follows:

Cumulative Sales Through LQ =

VAR CurrentQ =
CALCULATE(
MAX( ‘Date’[QuarternYear]),
‘Date’[Date]=TODAY() )

Return
CALCULATE(
[Cumulative Sales],
‘Date’[QuarternYear] < CurrentQ )

However, the new measure is now producing the same result as the cumulative sales measure, except it stops at the prior quarter. This makes sense given your dax, however I need to build some kind of dateadd into it, so it calculates the cumulative sales from one quarter prior. What do you think?

image

@pete.langlois,

Sorry if I’m being dense here , but I’m not understanding what you’re after. If you could please provide a mockup of the results you want to see, that would be a big help.

Thanks.

– Brian

No worries, here’s what I’m looking for:

Basically, if I’m looking at a line chart with cumulative sales for Q3, I want to be able to compare that data with the cumulative sales from Q2.

Does this make sense?

@pete.langlois,

Okay, got it now – thanks for the additional explanation and clarification. Will have a solution to you shortly.

– Brian

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

Is this the type of result you’re looking for? Below is the screenshot provided for the reference alongwith the reference formulas -

Resultant Output

Total Sales LQ = 
VAR TotalSalesLQ = 
CALCULATE( [Total Sales] , 
    DATEADD( Dates[Date] , -1 , QUARTER ) )

RETURN
IF( ISBLANK( [Total Sales] ) , 
    BLANK() , 
        TotalSalesLQ )




Cumulative Total Sales LQ = 
VAR CumulativeSalesLQ = 
CALCULATE( [Total Sales LQ] , 
    FILTER( ALLSELECTED( Dates ) , 
        Dates[Date] <= MAX( Dates[Date] ) ) )

RETURN
IF( ISBLANK( [Total Sales LQ] ) , 
    BLANK() , 
        CumulativeSalesLQ )

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Cumulative Totals - Harsh.pbix (659.2 KB)

1 Like

@pete.langlois,

Sorry - got tied up in a string of meetings before I could finalize and send you a solution, but looks like @Harsh has got you well covered (thanks, Harsh!).

  • Brian
1 Like

Hi @pete.langlois, 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 preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked 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 How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Hi @pete.langlois, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Thanks, Harsh!!! Nailed it, again.

Hello @pete.langlois,

You’re Welcome. :slightly_smiling_face:

I’m glad that we’re able to assist you on this and you found the solution helpful.

Thanks and Warm Regards,
Harsh