cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiugeS
Helper V
Helper V

If Filtered Date is Smaller then blank

Hi all,

 

Apologies if this is simple but i'm still learning.  

 

I have a table that contains issues (support tickets).  Sometimes these are resolved after the report period and I am trying to find a way that if the date is bigger then the Created Before slicer, it returns a blank value.  There's a measure included to keep blank resolved dates and I assume that measure could be modified.

 

Measure 2 is currently = If(MIN('Issues'[Created Date])>min('Created Date'[Date]),1,0) as I test but if the value is true I want blank returned and if false the 'Issues'[Resolved Date] returned.  I've attached the pbix for reference Pbix File 

Any help or guidance appreciated.

 

Screenshot_1.jpg

 

16 REPLIES 16
AllisonKennedy
Super User III
Super User III

Measure 2 = If(MIN('Issues'[Created])<min('Created Date'[Date]), BLANK(), SELECTEDVALUE('Issues'[Created]) )
 
That will give you equal or before selected date in Created Before slicer.
 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

az38
Super User II
Super User II

Hi @NiugeS 

= If(MIN('Issues'[Created Date])>min('Created Date'[Date]), BLANK(), SELECTEDVALUE('Issues'[Created Date]) )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Thank you for the help.  I tried to use the suggested measure but the table comes back blank.  Am I doing somethign wrong?

 

Measure 2 =
If(MIN('Issues'[Created])>min('Created Date'[Date]), BLANK(), SELECTEDVALUE('Issues'[Created]) )

 

Screenshot_3.jpg

 

@NiugeS  Your sample pbix file doesn't match your description? 

 

Try replacing BLANK() in the formula with a value like "A" and try putting the second argument in Selectedvalue, like: 

 

SELECTEDVALUE('Issues'[Created], "B")

So final result will be; 

 

Measure 2 =
If(MIN('Issues'[Created])>min('Created Date'[Date]), "A", SELECTEDVALUE('Issues'[Created], "B") )
 

What does the formula return now? 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

@AllisonKennedy  Apologies .. i've udpated the sample pbix file.

 

I tried the formula provided but get all A in the table despite changing the dates in the slicer:

 

Screenshot_4.jpg

As you have alread mentioned, you need to use the right dates. Change the dates in the Created Before slicer and you will see the A change. If you're seeing 'A" that means that the condition in your IF statement is TRUE, if you see 'B' that means the condition in your IF statement is false and there is more than one value selected for that date.

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

az38
Super User II
Super User II

@NiugeS 

and are you sure you attached the correct pbix-file?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Super User II
Super User II

@NiugeS 

try also

Measure 2 =
If(MIN('Issues'[Created])>min('Created Date'[Date]), BLANK(), MIN('Issues'[Created]) )

it depends what exactly  'Issues'[Created Date]  you need. Measure shoud has aggregated column 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Thanks for coming back to me.  I'm not sure what i'm doing wrong but neither measure provide any results.

az38
Super User II
Super User II

@NiugeS 

please, attach correct file


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Apologies if I copied the wrong file link.  I've updated the original message.

az38
Super User II
Super User II

@NiugeS 

much better

1 set Active "Show tems with no data" for [Key] field in Visualizations pane

2 re-write your measure

Measure 2 = 
var _Created = MIN('Issues'[Created])

RETURN
If(_Created > CALCULATE(MIN('Created Date'[Date])), BLANK(), SELECTEDVALUE('Issues'[Created]) )

 

but Im not sure I understand your condition logic correct

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 I think I should shut down the laptop for the night!  I didn't explain my logic correctly ... i wanted resolved date as you probably guessed!  


I will work on the suggestion you provided and see if I can make sense of it.  Thank you.

az38
Super User II
Super User II

@NiugeS 

Measure 2 = 
var _Created = MIN('Issues'[Created])
var _Resolved = MIN('Issues'[Resolved Date])

RETURN
If(_Created > _Resolved , BLANK(), _Created )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 

Thank you for your help.  I modified your previous suggestion before seeing the new one.  The below seems to provide the desired results.  Thank you for all your help.  I'll try the other measure as well - thanks.
Screenshot_5.jpg

 

 

az38
Super User II
Super User II

@NiugeS 

good luck! have a good night 🙂


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors