Write a measure with cumulated values until selected month

[HDEUISSS]

Hi everybody,

I am trying to write a measure and I just dont get it correctly.

I have a very simple sample to show my problem. This is my sample data:

Sample

What I want is, that people can select a single month and then all the sold objects are cumulated until the selected month for this year per seller.

I have tried different measures:

1.) I tried using a filter like this

SumUntil = 
var m = month(max('Sample'[Date]))
var y = year(max('Sample'[Date]))
return CALCULATE(sum('Sample'[Price]),filter(all('Sample'),and(YEAR('Sample'[Date]) = y,MONTH('Sample'[Date]) <= m)))

 the date filter works but because I used all I also loose the context of the seller. This is the result:
Date Filter

2.) I tried to remove the filter context only for the Date Month (Hierarchy) column:

SumUntil = 
var m = month(max('Sample'[Date]))
var y = year(max('Sample'[Date]))
return CALCULATE(sum('Sample'[Price]),all('Sample'[Date].[Monat]))

This way I get the correct filter for the seller but loose the year and month filter.

This is the result:

3.) I tried to combine both as this should be the way to go then, but I had no luck:

SumUntil = 
var m = month(max('Sample'[Date]))
var y = year(max('Sample'[Date]))
return CALCULATE(sum('Sample'[Price]),filter(all('Sample'),and(YEAR('Sample'[Date]) = y,MONTH('Sample'[Date]) <= m)),all('Sample'[Date].[Monat]))

can someone point me into the correct direction? What am I missing? Thanks!

[v-yalanwu-msft · Accepted solution]

Hi, @HDEUISSS ;

You could create a table by dax first.

seller = VALUES('Sample'[Seller])

Then create a measure.

SumUntil2 = 
IF(ISINSCOPE('seller'[Seller]),
CALCULATE(SUM('Sample'[Price]),FILTER(ALL('Sample'),MONTH([Date])<=MONTH(MAX('Sample'[Date]))&&YEAR([Date])=YEAR(MAX('Sample'[Date]))&&[Seller]=MAX('seller'[Seller]))),
CALCULATE(SUM('Sample'[Price]),FILTER(ALL('Sample'),MONTH([Date])<=MONTH(MAX('Sample'[Date]))&&YEAR([Date])=YEAR(MAX('Sample'[Date])))))

The final show:

[v-yalanwu-msft]

Hi, @HDEUISSS ;

Try it.

SumUntil = 
IF(ISINSCOPE('Sample'[Seller]),
CALCULATE(SUM('Sample'[Price]),FILTER(ALL('Sample'),MONTH([Date])<=MONTH(MAX('Sample'[Date]))&&YEAR([Date])=YEAR(MAX('Sample'[Date]))&&[Seller]=MAX('Sample'[Seller]))),
CALCULATE(SUM('Sample'[Price]),FILTER(ALL('Sample'),MONTH([Date])<=MONTH(MAX('Sample'[Date]))&&YEAR([Date])=YEAR(MAX('Sample'[Date])))))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HDEUISSS]

Hi @v-yalanwu-msft I tested it and it works as you said, but my expected output would be to show also people who sold something in january, but currently in shows only the cumulated sum for people that actually sold something in february.
But I need to show also people that sold something in the past (because its cumulated it need to catch all people).
Thanks for you help.

[HDEUISSS]

Hi@Greg_Deckler , thanks for your quick suggestion, unfortuntely, this way I loose for example the seller mike, which had sold something in January, when selecting February. other sellers that actually sold something in february are correct.

[Greg_Deckler]

@HDEUISSS Try:

SumUntil = 
  VAR __d = MAX('Sample'[Date])
  VAR __s = MAX('Sample'[Seller])
  VAR __t = FILTER(ALL('Sample'), [Date] <= __d && [Seller] = __s)
  VAR __r = SUMX(__t, [Price])
RETURN
  __r