Cumulative Total - Help

[chrisdotnisbet1]

Hi all,

I have a graph that shows the cumulative total of sales across the year:

CumulativeSales1 =

Calculate ( [TotalJobsVal],

    Filter(ALLSELECTED('cash (2)'),

    'cash (2)'[Received] <= MAX('cash (2)'[Received]

)))

This works fine, and has done since it was created.

My source data is used by many different dashboards - but it now has appended data added to it with a W/C date, rather than replacing the existing data.

The issue I am having, is that the cumulative total is looking at the whole dataset, not just the most recent w/c date.

How can I amend the above DAX to include the addtional filter of looking at the most recent week?

In the source data, it is 'cash (2)'[W/C Date]

Any help would be greatly appreciated

[v-jingzhang]

Hi @chrisdotnisbet1 , does your data look like below now? The old data doesn't have W/C Date data and the new appended data doesn't have Received data. 

If so, you could combine these two dates into one column and use this new date column in your measures.

DAX method:

Column = MAX('Table'[Received],'Table'[W/C Date])

M method:

= Table.AddColumn(Source, "Custom", each if [Received] > [#"W/C Date"] then [Received] else [#"W/C Date"])

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[chrisdotnisbet1]

Apologies in delay in getting back to you - Ive had formatting issues with bad HTML(?)

Job Number      Received       SalePrice         W/C Date
1                        01/01/2020   £1000             21/12/2020
2                        01/02/2020   £500               21/12/2020
1                        01/01/2020   £1000             28/12/2020
2                        01/02/2020   £500               28/12/2020
3                        01/03/2020   £750               28/12/2020
1                        01/01/2020   £1000             04/01/2021
2                        01/02/2020   £500               04/01/2021
3                        01/03/2020   £750               04/01/2021
4                        01/04/2020   £1500             04/01/2021

I want to use only the data with the max w/c date, then do a cumulative total graph based on the the sales date (Received Date)

I hope this makes sense?

[v-jingzhang]

@chrisdotnisbet1Has this problem been solved? If so, kindly Accept the appropriate reply as the solution or post your own solution. More people will benefit from it. Otherwise, can you share more details about how it is going now? Thanks.

[v-jingzhang]

@chrisdotnisbet1 yes, it helps a lot. Please try below measure. You can use && to combine multiple filters in a FILTER() function.

CumulativeSales = 
CALCULATE (
    SUM ( 'cash (2)'[SalePrice] ),
    FILTER (
        ALLSELECTED( 'cash (2)' ),
        'cash (2)'[W/C Date] = MAX ( 'cash (2)'[W/C Date] )
            && 'cash (2)'[Received] <= MAX ( 'cash (2)'[Received] )
    )
)

Kindly let me know if this helps.
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@chrisdotnisbet1 , Do you week only cumulative of one week?

Try like

CumulativeSales1 =
var _weekstart = 'cash (2)'[Received]+-1*WEEKDAY('cash (2)'[Received],2)+1
return
Calculate ( [TotalJobsVal],
Filter(ALLSELECTED('cash (2)'),
'cash (2)'[Received] <= MAX('cash (2)'[Received] && 'cash (2)'[Received] >=_weekstart
)))

Assumed Received is a date

[chrisdotnisbet1]

@amitchandak 

This measure is used to show the revenue across the year, however the value in the most recent week's dataset is £43.7m

The value is incorrect due to it looking across the whole dataset.

With the other measures I am using, I was able to filter them by using the following:

ABC = CALCULATE([XXX],

filter('cash (2)', 'cash (2)'[W/C Date] = max('cash (2)'[W/C Date]))

I am looking to filter the dataset down to max('cash (2)'[W/C Date]), then look at the cumulative monthly figures as I was doing previously.

When using your above suggestion, it could not find Received, as it would only accept a measure.

Received is a date and is the date that the sale was generated.

[amitchandak]

@chrisdotnisbet1 ,Can you share sample data and sample output in table format?

[chrisdotnisbet1]

@amitchandak 

I created a sample of the data in a previous reply