Dax If date greater than formula

[Anonymous]

Hello,

I am trying to calculate the number of leads that have a qualification date beyond a specific date and have a certain field marked as yes. 

Measure = sumx('Leads',IF(AND('Leads' [qualification__date__c] > 12-01-2019, 'Leads' [SOE] = "Yes"), 1,0))

This measure however, does not seem to be accurately counting the leads and I believe it has something to do with the lead qualification date statement. When I  put the calculation on a card visualization and apply the date filter outside of the formula, the number is accurate. I do not want to use outside filters, however, because I will be adding on to this formula to make it a ratio and I do not want that filter to affect the denominator in the ratio.

Is this the accurate way to include greater than date in an IF function? Is there another way I can get the same result using different formulas?

Thank you!

Katy

[amitchandak · Accepted solution]

Assume you have date from date calendar as filter, try like

Measure = 
var _max =minx(date,date[date])
return
sumx('Leads',IF(AND('Leads' [qualification__date__c] > _max, 'Leads' [SOE] = "Yes"), 1,0))	

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak Thank you for the response. I am a little new to PowerBI so can you help me understand your solution a little better? From what I understand you are setting _max as the output for the minx calculation. Can you help me understand how minx works and what i should inputting into it as far as table and column names. 

Thanks for the help!

[amitchandak]

Typically Min or max will not allow expression while doing the calculation. Also when we take some slicer value minx/maxx is a better option.

https://docs.microsoft.com/en-us/dax/minx-function-dax

The idea was that you have slicer , where you have taken a date or range of dates. As it can be range taking mi. Typically in power BI, we suggest to have a date calendar . Assuming that the minx was on date[date]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/