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

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

    No you didn't misunderstand. I think you're getting to the point of my question.

     

    However, I know this is not working properly because my headcount for the actual month is 20K and the result of this expression is 37K. 

    Moreover, I figured out this is responding to the selection, but it's not correctly calculating for the 'default' month, I also added the year to the expression to ensure it is calculating for the right year, but it's still not working. 

    test3 = CALCULATE(
    DISTINCTCOUNT ( Link[Employee Nr] ),
    FILTER ('Calendar',MONTH('Calendar'[Id_Date]) = MONTH(LASTDATE ( 'Calendar'[Id_Date]))),
    FILTER('Calendar', YEAR('Calendar'[Id_Date]) = YEAR(LASTDATE ( 'Calendar'[Id_Date])))
    )

  • inescastelhano's avatar
    inescastelhano
    Frequent Visitor
    9 years ago

    Should be something like this but it doesn't work:

    Headcount = CALCULATE(
    DISTINCTCOUNT ( Link[Employee Nr] ), 'Calendar'[IsLatestYearMonth]=if(MONTH('Calendar'[Id_Date]) = MONTH(LASTDATE ( 'Calendar'[Id_Date])),0,1),
    FILTER ('Calendar',MONTH('Calendar'[Id_Date]) = MONTH(LASTDATE ( 'Calendar'[Id_Date]))),
    FILTER('Calendar', YEAR('Calendar'[Id_Date]) = YEAR(LASTDATE ( 'Calendar'[Id_Date])))
    )

     

    but I get an error saying "The expression contains multiple columns, but only a single column can be used in a True/False epression that is used as a table filter expression".

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

      inescastelhano

       

      you dont need to create 2 filters (it will behave like an or then) 

       

      Headcount =
      CALCULATE (
      DISTINCTCOUNT ( Link[Employee Nr] ),
      'Calendar'[IsLatestYearMonth]
      = IF (
      MONTH ( 'Calendar'[Id_Date] ) = MONTH ( LASTDATE ( 'Calendar'[Id_Date] ) ),
      0,
      1
      ),
      FILTER (
      'Calendar',
      MONTH ( 'Calendar'[Id_Date] ) = MONTH ( LASTDATE ( 'Calendar'[Id_Date] ) )
      && YEAR ( 'Calendar'[Id_Date] ) = YEAR ( LASTDATE ( 'Calendar'[Id_Date] ) )
      )
      )
      • inescastelhano's avatar
        inescastelhano
        Frequent Visitor
        9 years ago

        I get an error saying "The expression contains multiple columns, but only a single column can be used in a True/False epression that is used as a table filter expression"...