Filter by Max Date or Selected Date

[inescastelhano]

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)

[Anonymous · Accepted solution]

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]

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!

[vanessafvg]

@inescastelhano  sorry got distracted, did Xiaoxin solution work for you?

[vanessafvg]

@inescastelhano 

you might have to use an if statement but maybe lastdate will work for you?

https://msdn.microsoft.com/en-us/library/ee634380.aspx

[inescastelhano]

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

[inescastelhano]

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]

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

[#9]

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

[inescastelhano]

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]

@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]

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"...

[inescastelhano]

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])))
)