cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rogerh
Helper II
Helper II

IF Specific Value is Select in Filter do one thing, if any other value is selected do something else

Hi All,

 

Can I please have some help with the following? I created a measure that projects our monthly orders. 

If I select the current month using a slicer it take our To Date Projected Orders, divided by MTD Order Target, then multiplies the result by the Total Order Target to give us a projetion. If I select a past month it does the same calculation and gives me the correct result for the previous months. 

 

However when I select both this month and last month (as I want to get a view of the quarter) it does the full calculation over both months target and orders and gives me the wrong result.

 

I would like to add an IF statement saying, IF the month in the Slicer is this month do the following. IF it isnt this month, just give me the 'Accepted Orders'.

 

I tried Selectedvalue but this doesnt seem like the correct function. Below is my current measure.

 

Test = DIVIDE([HE_W_Order_Proj_Accpt_MTD], // To Date Projected Accepted Orders
CALCULATE(SUM('Platform - Targets'[Accepted Order Target]) ,
FILTER('Platform - Targets' , 'Platform - Targets'[Target ID] = "2" && 'Platform - Targets'[In the Past] = "Y"))) // To Date Target
*
CALCULATE(SUM('Platform - Targets'[Accepted Order Target]) ,
FILTER('Platform - Targets' , 'Platform - Targets'[Target ID] = "2" )) // Total Target

 

Is anyone able to help or point me in the right direction?

Thanks

Roger

1 ACCEPTED SOLUTION

Hi @Rogerh ,

 

"Thank you for your reply. It might be worth simplifying my request. 
Is there a Dax function that will allow you say.

If the slicer has the current month selected, do one thing. If it has anything else selected do something else. I should be able to work it out if I can find the correct function. Otherwise happy to put more detail in and send it over. Cheers"

v-lionel-msft_1-1612853794112.png

When you select multiple months, do you want the measure results to be aggregated again?

v-lionel-msft_3-1612853969665.png

Maybe you can create another measure like this.

Sum of Test = 
SUMX(
    'Platform - Targets'
    [Test]
)

 

Best regards,
Lionel Chen

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

3 REPLIES 3
amitchandak
Super User
Super User

@Rogerh , Not very clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

How are you getting MTD ?

If you are using time intelligence then

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

You can get last month
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak 

Thank you for your reply. It might be worth simplifying my request. 
Is there a Dax function that will allow you say.

 

If the slicer has the current month selected, do one thing. If it has anything else selected do something else. I should be able to work it out if I can find the correct function. Otherwise happy to put more detail in and send it over. Cheers

Hi @Rogerh ,

 

"Thank you for your reply. It might be worth simplifying my request. 
Is there a Dax function that will allow you say.

If the slicer has the current month selected, do one thing. If it has anything else selected do something else. I should be able to work it out if I can find the correct function. Otherwise happy to put more detail in and send it over. Cheers"

v-lionel-msft_1-1612853794112.png

When you select multiple months, do you want the measure results to be aggregated again?

v-lionel-msft_3-1612853969665.png

Maybe you can create another measure like this.

Sum of Test = 
SUMX(
    'Platform - Targets'
    [Test]
)

 

Best regards,
Lionel Chen

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

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!