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 filter by the max year-month that exists in the table. I am already doing this with a calculated column which formula is: IsLatestYearMonth = if('Calendar'[MonthID]=max('Calendar'[MonthID]), 1, 0 )

 

- if one month is selected, I would like to show the count of employees filtered for that month. I know one can do this by using: 

test = IF (HASONEVALUE ('Calendar'[MonthID]),VALUES ('Calendar'[MonthID]),0)

 

Now, I am trying to integrate both formulas together into this one: 

 

Headcount = calculate(DISTINCTCOUNT(Link[Employee Nr]), SOMETHING TO BE FILTERED BY DATE)

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

11 Replies

    • inescastelhano's avatar
      inescastelhano
      Frequent Visitor
      9 years ago

      Hi vanessafvg, however LASTDATE receives and returns an argument of type DATE and there is no way for me to know the MonthID for that date, since I have several years. I can understand how to use the LASTDATE to get the last month with

      Month(LASTDATE('Calendar'[Id_Date]))

      , but not how to use it for filtering in CALCULATE expression. 

       

      Thank you, 

      Inês 

      • vanessafvg's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Headcount = calculate(DISTINCTCOUNT(Link[Employee Nr]), SOMETHING TO BE FILTERED BY DATE)

         
         
        like this maybe?
         
        Headcount =
        CALCULATE (
        DISTINCTCOUNT ( Link[Employee Nr] ),
        FILTER (
        datetable,
        MONTH ( 'Calendar'[Id_Date] ) = MONTH ( LASTDATE ( 'Calendar'[Id_Date] ) )
        )
        )
         
        unless i misunderstand what you asking for
         
    • inescastelhano's avatar
      inescastelhano
      Frequent Visitor
      9 years ago

      Hi vanessafvg, moreover I get an error saying "A function LASTDATE has been used in a True/False expression that is used as a table filter expression. This is not allowed."

       

      Thank you

  • 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!