Calculate with slicer date

[kalspiros]

Hi all,

I'm sorry, i'm sure the answer has been addressed again but from some quick search, i couldn't find it 😕

And TOTALYTD doesn't seem to do the trick for me.

So:

I'm trying to capture the sum of all [days] an employee was off sick from the beginning of the year until the slicer date.

Slicer Month:= CALCULATE(MIN('Leaves'[Month]))

CSP so far this year:= CALCULATE(SUMX('Leaves',IF('Leaves'[Month]<=[Slicer Month],[days],0)),ALL('Leaves'[Month Name]))

i've also tried

CSP so far this year:= CALCULATE(SUM([days]), ALL('Leaves'[Month Name]),FILTER('Leaves','Leaves'[Month]<=[Slicer Month]))

None of them returns the correct value.

I'm so confident the answer would be a "d-uh"! moment for me so apologies for my ignorance!

Many thanks in advance

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

RT month =

var _year = YEAR(MAX(Aggregation[Opened At]))

var _month = MONTH(MAX(Aggregation[Opened At (Month)]))

return

CALCULATE(SUM(Aggregation[Duration(Secs)]),ALL(Aggregation),MONTH(Aggregation[Opened At (Month)])<=_month,YEAR(Aggregation[Opened At (Month)]=_year))

The example is a bit different but the base logic is the same. Ping me with @ if you have questions about it.I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[kalspiros]

@ValtteriN that works perfect! many thanks! interested on your opinion, why my second measure above didn't work? The [Month] is just the number of the month, so is the slicer. Kind of happy to see that i wasn't miles away 😄 

Thank you once again!

[ValtteriN]

@kalspiros  The key difference is that you used FILTER. FILTER creates a table filtered with the conditions while using filter conditions in CALCULATE modify the filter context.