Forum Discussion

inescastelhano's avatar
inescastelhano
Frequent Visitor
9 years ago
Solved

Filter by Max Date or Selected Date

Hello everyone,    I am new to power BI and I am trying to do the same we can do in Qlikview. So, what I want is to show the count of employees, but: - if no months are selected, I would like to ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi inescastelhano,


    You can try to use below formula if it suitable for your requirement:

     

    Result = IF(COUNTROWS('Calendar')<>COUNTROWS(ALL('Calendar')),//check all selected or not select
	"Distinct Count: "&CALCULATE(DISTINCTCOUNT('Link'[Employee]),VALUES('Calendar'[Date])),
	"Last Month: "&MONTH(LASTDATE('Calendar'[Date])))

     

    Notice: you should ensure these tables contains relationship and the cross filter direction is setting to 'both'.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi inescastelhano,


You can try to use below formula if it suitable for your requirement:

 

Result = IF(COUNTROWS('Calendar')<>COUNTROWS(ALL('Calendar')),//check all selected or not select
	"Distinct Count: "&CALCULATE(DISTINCTCOUNT('Link'[Employee]),VALUES('Calendar'[Date])),
	"Last Month: "&MONTH(LASTDATE('Calendar'[Date])))

 

Notice: you should ensure these tables contains relationship and the cross filter direction is setting to 'both'.

 

Regards,

Xiaoxin Sheng

inescastelhano's avatar
inescastelhano
Frequent Visitor
9 years ago

Thank you Anonymous! I ended up with the formula:

IF(COUNTROWS('Calendar')<>COUNTROWS(ALL('Calendar')),
"Distinct Count: "&CALCULATE(DISTINCTCOUNT('Link'[Employee Nr]),VALUES('Calendar'[Id_Date])),
"Last Month: "&calculate(DISTINCTCOUNT(Link[Employee Nr]), 'Calendar'[IsLatestYearMonth]=1))

 

Anyway your solution took me to the right place.

 

Thank you so much!