Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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.

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

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
Not applicable

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

 

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

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.
Anonymous
Not applicable

Creating separate table worked. Thanks bro.

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.