Forum Discussion
Helper ICalculate with slicer date
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
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)]))returnCALCULATE(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/
3 Replies
- ValtteriN
Community Champion
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)]))returnCALCULATE(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/
