Latest Value in Slicer

[Anonymous]

I have two tables (Dates and Demographics) of which there is a relationship in the dates column from both table.

From the Dates table, i have two different slicer, Month and Year, where multiple value could be selected.

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2018

2019

2020

The demographic data is build in this way:

Date	I.D	Nationality
1/1/2018	A	American
1/1/2018	B	Japanese
1/2/2018	A	American
1/2/2018	C	Korean

I am trying to build a table to show the break down of headcount by nationality. 

I am using this measure to calculate the headcount: calculate(counta(I.D),filter(Demographic, demographic[date] = max(demographic[date])))

However, the result that i get is not what i wanted. 

For instance, Feb and 2018 is selected in the slicer, what the tables shows is:

Nationality	Headcount
Japanese	1
American	1
Korean	1

where Japanese should not appear.

So right now i am trying to match the month(demographic[date]) year(demographic[date])= max([ selected month])max([selected year]) instead which i cannot seem to do it.

Please advice. 

[Icey]

Hi @Anonymous ,

Just as @amitchandak said, if you have created relationship between the two tables, you only need to create a simple measure.

headcount = COUNT(demographic[I.D])

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey , 

You got my issue wrong, what i want it to show is the data based on the latest date (without clicking on to the slicer showing 1/2/2018).  What you are showing me is the count of every nationality at hand.

So what i want the table to show is 1 american 1 korean when the slicer is not selected.

I want to show the audience of the report the most up to date info as it appear and should they want to see the other dates, they will then toggle around the slicer.

Thank you.

[Icey · Accepted solution]

Hi @Anonymous ,

Sorry. Please check if this is what you want:

headcount =
VAR LatestDate =
    MAXX ( ALL ( demographic ), demographic[Date] )
RETURN
    IF (
        ISFILTERED ( Dates[Year] ) || ISFILTERED ( Dates[Month] ),
        COUNT ( demographic[I.D] ),
        CALCULATE ( COUNT ( demographic[I.D] ), demographic[Date] = LatestDate )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , If you date table if Date joined on date and using month and year from there only Count should do.

calculate(counta([I.D]))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.