cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

 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

Pangeatech

1 ACCEPTED SOLUTION
Community Support

Hi @RavirajMSE ,

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.

5 REPLIES 5
Community Support

Hi @RavirajMSE ,

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.

Frequent Visitor

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,

Super User

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

Frequent Visitor

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,

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors