Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
pearlyred
New Member

Grouping by date category using slicer date

Hi all,

 

I have a data table that includes these columns:

NameDue DateCompleted Date


I also have a calendar table ('Calendar Lookup') that feeds a single-select slicer with the month-end date.

I want to show a status based on the difference of Due Date and Slicer Date. I believe because I'm using the slicer date and not a static date, that I can't do anything with a DAX column and must use a measure.

 

I've created a grouping table ('Grouping Lookup'):

pearlyred_0-1696455388946.png

 

And I'm currently using this measure to determine the status:

 

Due Status = 
VAR Overdue =
    CALCULATE(
        [Total items],
            DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) > 0
    )
VAR DueSoon =
    CALCULATE(
        [Total items],
            DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) > -7 &&
            DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) <= 0
    )
VAR OnTrack =
    CALCULATE(
        [Total items],
            DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) <= -7
    )
RETURN
SWITCH(
    TRUE(),
    MAX('Grouping Lookup'[Order]) = 3,
    Overdue,
    MAX('Grouping Lookup'[Order]) = 2,
    DueSoon,
    OnTrack
)

 

pearlyred_1-1696455799159.png

 

This seems to work ok, but the downside is, selecting any of the statuses in that visualisation, doesn't filter the other page visualisations.

 

Is there a way to rework how I've done this so that the filters apply across visualisations on the page?

 

thanks!

1 REPLY 1
lbendlin
Super User
Super User

selecting any of the statuses in that visualisation, doesn't filter the other page visualisations

That is correct, you cannot impact the data model with a measure.  However, you can use measures as visual filters, and you can create a disconnected table with all the possible values for that measure. This combination will then allow you to "show everythign that is on track"  etc.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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