Last Value of Each Person By Date Dynamically

[Anonymous]

Hi, 

I have been searching in the Power BI community and on Google for a way to get this figured out but have failed at every similar suggestions. 

I am trying to create a bar chart to show the (number of) people who answered yes to the question vs the (number of) people who answered no but dynamic by a date slicer. 

I would have a date slicer, a bar chart, and a table of names.

Date slicer shows a range and allows users to change the end date, with a title saying "As Of". 

Bar chart will have yes/no on the y-axis and x-axis showing the number of people. 

The total of the two bars would be the total number of people that have submitted an answer at all.

It would capture ONLY the last response of each person.

A person could answer no the day before but yes for today; it should only count the yes for this person.

To further illustrate this, I think a sample data will help.

Below is a sample data of what the format would look like.

With this data, if the date slicer has the end date of 7/2/2020, the total number of people of the two bars combined would be 4.

The number of people for answer Yes would be 1 (C), while the number of people for answer No would be 3 (A, B, C). 

This is because:

• A answered No on 7/2.
• B answered No on 7/1.
• C answered Yes on 7/2.
• D answered No on 7/1.

Name	Date	Work In Office
A	7/1/2020	No
A	7/2/2020	No
A	7/3/2020	Yes
A	7/4/2020	No
B	7/1/2020	No
B	7/3/2020	No
C	7/2/2020	Yes
C	7/4/2020	Yes
C	7/5/2020	Yes
D	7/1/2020	No
D	7/3/2020	Yes
D	7/4/2020	Yes

I tried things like the lastnonblank function and filter the table by the table and get max date of each person. 

If you could also tell me how I could count the number of people who has EVER answer yes before, that would be even more amazing.

Thank you for reading and considering possible solutions! 
I will keep trying.

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Firstly I build a slicer table as below.

Slicer = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Build a slicer

Result:

And I build a Work in Office Table as below.

Work in Office = VALUES('Table'[Work In Office])

Then I build a measure to achieve your goal.

Measure =

COUNTROWS (

    FILTER (

        VALUES ( 'Table'[Name] ),

        CALCULATE (

            IF (

                ISEMPTY (

                    FILTER (

                        'Table',

                        'Table'[Date] <= MAX ( Slicer[Date] )

                            && 'Table'[Work In Office] = "Yes"

                    )

                ),

                "No",

                "Yes"

            )

                = SELECTEDVALUE ( 'Work in Office'[Work In Office] )

        )

    )

)

Build Stacked bar chart as below:

The result is correct.

You can download the pbix file from this link:  Last Value of Each Person By Date Dynamically

Best Regards,

Rico Zhou

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

[Ashish_Mathur]

Hi,

Has the other contributor answered your question?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you Ashish_Mathur!

That solves my question about having a dynamic values with date slicer (last answer on and last answer was).

However, the countrow for Measure isn't working quite right. 

If you change the date slicer to 7/1 - 7/3, you get two bars of 3.

It should be 3 yes and 1 no.

Any suggestions on how to fix this?

[amitchandak]

@Anonymous , Try like

Yes Employee = countx(filter(summarize(Table, Table[Name], "_1",lastnonblankvalue(Table[Date],max(Table[Work In Office]))),[_1] ="Yes"),[Name])
No Employee =countx(filter(summarize(Table, Table[Name], "_1",lastnonblankvalue(Table[Date],max(Table[Work In Office]))),[_1] ="No"),[Name])