cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisdotnisbet1
Helper I
Helper I

Cumulative Total - Help

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
8 REPLIES 8
v-jingzhang
Community Support
Community Support

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. 

010701.jpg

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"])

 

010702.jpg

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.

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?

@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.

@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
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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

chrisdotnisbet1_0-1609853375019.png

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.

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

I created a sample of the data in a previous reply

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.