cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SonaSingh123 Regular Visitor
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
Community Support Team

Re: Dynamic slicer value calculation

hi, @SonaSingh123 

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 Contributor
Super Contributor

Re: Dynamic slicer value calculation

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 )
)
SonaSingh123 Regular Visitor
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.
Please help

 

amitchandak Super Contributor
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 ))

SonaSingh123 Regular Visitor
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
Community Support Team

Re: Dynamic slicer value calculation

hi, @SonaSingh123 

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

SonaSingh123 Regular Visitor
Regular Visitor

Re: Dynamic slicer value calculation

Creating separate table worked. Thanks bro.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 6 members 1,001 guests
Please welcome our newest community members: