cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Calculate 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!

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

3 REPLIES 3
Super User

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!

Proud to be a Super User!

Helper I

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

Super User

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

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors