Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a graph that shows the cumulative total of sales across the year:
Hi @Anonymous , 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.
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?
@AnonymousHas 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.
@Anonymous 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.
@Anonymous , 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
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.
@Anonymous ,Can you share sample data and sample output in table format?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |