How to segregate slicer based on different column for one day before the selected date.

[RavirajMSE]

Suppose I have three Columns of a school attance sheet, I want To make slicer which will represent todays absent students based on date filter or by default today.

Now If want to add one more slicer which segregate the students who are absent today in two sections .

First Section

in the first section it should represent those students who were also absenty yesterday out of today's absenteesm,

and in section it will show the students from today's absent who were not absent yesterday,.

please help me out.

Date	Student	Attandance
22-11-2022	John	Absent
22-11-2022	Ram'	Present
22-11-2022	Rak	Present
22-11-2022	Tutu	Absent
22-11-2022	Ravi	Absent
22-11-2022	Teja'	Absent
22-11-2022	Vishesh	Present
21-11-2022	John	Present
21-11-2022	Ram'	Absent
21-11-2022	Rak	Present
21-11-2022	Tutu	Absent
21-11-2022	Ravi	Present
21-11-2022	Teja'	Present
21-11-2022	Vishesh	Absent
21-11-2022	John	Absent
20-11-2022	Ram'	Present
20-11-2022	Rak	Present
20-11-2022	Tutu	Absent
20-11-2022	Ravi	Absent
20-11-2022	Teja'	Absent
20-11-2022	Vishesh	Absent

Thanks & Regards

Raviraj Kumar Gupta

Business Analyst 

Pangeatech

[v-xiaosun-msft · Accepted solution]

Hi @RavirajMSE ,

According to your description, I made the sample and here is my solution. Please follow these steps.

Create a measure to return "Today Absent".

Today Absent =
VAR _slice =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _last =
    SELECTEDVALUE ( 'Table'[Date] ) - 1
RETURN
    CALCULATE (
        MAX ( 'Table'[Student] ),
        FILTER ( 'Table', 'Table'[Date] = _slice && 'Table'[Attandance] = "Absent" )
    )

Create a column to return the minimum of the DATE.

min date =
MIN ( 'Table'[Date] )

Create a measure to return the "Yesterday Absent".

Yesterday Absent =
VAR _slice =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _last =
    IF ( _slice > MAX ( 'Table'[min date] ), _slice - 1 )
RETURN
    CALCULATE (
        MAX ( 'Table'[Student] ),
        FILTER (
            ALL ( 'Table'[Date], 'Table'[Attandance] ),
            'Table'[Date] = _last
                && 'Table'[Attandance] = "Absent"
        )
    )

Create a measure to return the "Yesterday Present".

Yesterday Present =
VAR _slice =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _last =
    IF ( _slice > MAX ( 'Table'[min date] ), _slice - 1 )
RETURN
    CALCULATE (
        MAX ( 'Table'[Student] ),
        FILTER (
            ALL ( 'Table'[Date], 'Table'[Attandance] ),
            'Table'[Date] = _last
                && 'Table'[Attandance] = "Present"
        )
    )

Then put "Student", "Today Absent", "Yesterday Absent" into a table and select "is not blank" in the filter pane.

And put "Student", "Today Absent", "Yesterday Present" into another table and select "is not blank" in the filter pane.

You will get the expected output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[RavirajMSE]

Hey,

Thanks for your Suggestion, But it will return the count of those present and absent students, But I need the name of those students,

[amitchandak]

@RavirajMSE ,

Today Absent = CALCULATE(count('Table'[Student]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])), filter(Table, Table[Attandance] = "Absent") )

yesterday Absent = CALCULATE(count('Table'[Student]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1 ), filter(Table, Table[Attandance] = "Absent") )

Absent both days  = countx(filter(Values(Table[Student]), not(isblank([yesterday Absent])) && not(isblank([Today Absent])) ) , [Student])

Absent yesterday Present today = countx(filter(Values(Table[Student]), not(isblank([yesterday Absent])) && (isblank([Today Absent])) ) , [Student])

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[RavirajMSE]

@amitchandak 

Hey,

Thanks for your Suggestion, But it will return the count of those present and absent students, But I need the name of those students,

[amitchandak]

@RavirajMSE , Plot this measure with student's name in a visual. In a visual use this measure with student name