Dynamic slicer value calculation

[Anonymous]

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'

[amitchandak]

Please refer

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 )
)

[Anonymous]

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.

Please help

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

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

Then adjust your formula

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.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

Creating separate table worked. Thanks bro.

[amitchandak]

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

[Anonymous]

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.