cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Dynamic slicer value calculation

I want to create a DAX formula for the following query. The year and month is dynamic, user can select it from the slicer. Can you please help on this.

SELECT COUNT([IMOT])
FROM [IMOT]
WHERE [IMOT] = 'Arrear'
AND [Dispatch Closing Date_IMOT] >= '2018-01' AND [Year-Month] < '2018-01'
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dynamic slicer value calculation

For your case, I think you need a separate Year-Month table, and don't create the relationship with IMOT table.

VAR D = SELECTEDVALUE( Year-Month[Year-Month] )
VAR C = CALCULATE( COUNT( IMOT[FP-IMOT] ), IMOT[FP-IMOT] = "Arrear" )

Return CALCULATE( C, filter(sd_IMOT,sd_IMOT[Dispatch Closing Date_IMOT] >= D && sd_IMOT[Year-Month_IMOT] < D ))

Then it should work, If not your case, please share some sample data and your expected out.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super Contributor

## Re: Dynamic slicer value calculation

```Sales Days = (
VAR _Cuur_start = Min('Compare Date'[Compare Date])
VAR _Curr_END = Max('Compare Date'[Compare Date])
return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)```
Regular Visitor

## Re: Dynamic slicer value calculation

But in my scenario, I want to filter based on two date columns - Dispatch-Closing-Date and Year-Month.

But calculate function is accepting only one condition.

I tried the following formula.

VAR D = SELECTEDVALUE( IMOT[Year-Month] )
VAR C = CALCULATE( COUNT( IMOT[FP-IMOT] ), IMOT[FP-IMOT] = "Arrear" )

Return CALCULATE( C, sd_IMOT[Dispatch Closing Date_IMOT] >= D)
+
CALCULATE( C,sd_IMOT[Year-Month_IMOT] < D )

Instead of adding two calculate functions, I want to add both conditions in a single calculate function.
Is there any alternative way to do the same.

Super Contributor

## Re: Dynamic slicer value calculation

Return CALCULATE( C, filter(sd_IMOT,sd_IMOT[Dispatch Closing Date_IMOT] >= D && sd_IMOT[Year-Month_IMOT] < D ))

Regular Visitor

## Re: Dynamic slicer value calculation

But this filtering is not happening, when am selecting Year-Month value from the slicer. Instead of the calaculation, its showing amount belongs to that particular Year-Month.

Community Support Team

## Re: Dynamic slicer value calculation

For your case, I think you need a separate Year-Month table, and don't create the relationship with IMOT table.

VAR D = SELECTEDVALUE( Year-Month[Year-Month] )
VAR C = CALCULATE( COUNT( IMOT[FP-IMOT] ), IMOT[FP-IMOT] = "Arrear" )

Return CALCULATE( C, filter(sd_IMOT,sd_IMOT[Dispatch Closing Date_IMOT] >= D && sd_IMOT[Year-Month_IMOT] < D ))

Then it should work, If not your case, please share some sample data and your expected out.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Dynamic slicer value calculation

Creating separate table worked. Thanks bro.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 1,001 guests
Recent signins: