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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
b2wise
Helper III
Helper III

Optimize DAX: Filter Date Table by Max Date of Different Table

Hi All,

 

I am trying to filter a Date slicer visual to only allow selections up to the month previous to the max date in another table. I have two tables that I don't want the most recent month of those two tables available for selection, just the previous month and older.

 

I made a measure which is kind of working but not returning the right results. As you can see in this image only four months are available for selection.

 

image.png

 

This is my measure applied to the visual as "Show items when greater than 0"

 

Date Slicer Limiter =
--Limit date slicer to previous month from latest month date
VAR maxpunchdate =
MAX ( Punch_Report[DATE] )
VAR maxtemphours =
MAX ( 'Temp Hours'[Invoice Date] )
VAR minmonth = -- If one max date is Oct 21 and the other Sept 21 I don't want either available only Aug 21 and older
MONTH ( MIN ( maxpunchdate, maxtemphours ) ) 
VAR minyear = -- same as minmonth
YEAR ( MIN ( maxpunchdate, maxtemphours ) )
RETURN
CALCULATE (
COUNTROWS ( Dates ),
VALUE(LEFT(Dates[DateInt],6)) < VALUE( (minyear )
& ( minmonth )), ALL(Dates)
)
 
These are sample tables:
 
Temp Hours
DateHours
9/5/202136
10/7/201835
10/14/201826

 

Punch_Report

DATETOTAL
9/25/20211.5
9/24/20215.32
9/23/20215.82

 

Date Table Sample

DateMonth & YearDateInt
8/3/2021Aug-2120210803
9/7/2021Sep-2120210907
10/5/2021Oct-2120211005

 

I'm pretty certain the reason it isn't working is because DAX MONTH is returning 1 for January not 01 and DateInt field always includes the 0.

 

If anyone can help me with this or has a better and more efficient way of filtering I would greatly appreciate it!

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

How about this?

 

Define

prevmonth = 
VAR maxpunchdate = CALCULATE ( MAX ( Punch_Report[DATE] ), ALL ( Dates ) )
VAR maxtemphours = CALCULATE ( MAX ( 'Temp Hours'[Date] ), ALL ( Dates ) )
RETURN
   EOMONTH ( MIN ( maxpunchdate, maxtemphours ), -1 )

 

And then use [Date Slicer Limiter] is 1 as your filter where

Date Slicer Limiter = IF ( MAX ( Dates[Date] ) <= [prevmonth], 1, 0 )

 

View solution in original post

7 REPLIES 7
AlexisOlson
Super User
Super User

I'm not completely sure how all your date columns types are set up but how about something like this?

 

Date Slicer Limiter =
VAR maxpunchdate = MAX ( Punch_Report[DATE] )
VAR maxtemphours = MAX ( 'Temp Hours'[Invoice Date] )
VAR prevmonthend = EOMONTH ( MIN ( maxpunchdate, maxtemphours ), -1 )
RETURN
    CALCULATE (
        COUNTROWS ( Dates ),
        Dates[Date] <= prevmonthend 
    )

@AlexisOlson  Thank you so much, your DAX is much simpler. Still having issues though...

 

The date slicer is filtered but the available dates for selection are Feb 2020 through Oct 2021 despite the date table starting in Jan 2019 and  VAR prevmonthend = 9/30/2021. 

 

This is what I get when I RETURN prevmonthend instead of COUNTROWS.

 

image.png image.png

And this is what I get with the meausre you wrote using COUNTROWS.

 

image.png

 

I don't know why Oct 2021 is being included and my only guess at why it only starts in Feb 2020 is that the Punch_Report tables starts in Jan 2020 though it ends in Oct 2021.

 

Thank you for your help

 

Anyone have any ideas why my date table is not filtering properly?

I can't tell from the screenshots. If you share a link to a .pbix, I can probably take a look.

@AlexisOlson  Thanks so much.

As you can see in the sample file only September 2021 is available for selection when really the only dates that should be available are August 2021 and older as per the measure.

 

https://drive.google.com/file/d/1E-xu5Ux1XHh5y_94QytQ5m2U44-ZUgKx/view?usp=sharing 

How about this?

 

Define

prevmonth = 
VAR maxpunchdate = CALCULATE ( MAX ( Punch_Report[DATE] ), ALL ( Dates ) )
VAR maxtemphours = CALCULATE ( MAX ( 'Temp Hours'[Date] ), ALL ( Dates ) )
RETURN
   EOMONTH ( MIN ( maxpunchdate, maxtemphours ), -1 )

 

And then use [Date Slicer Limiter] is 1 as your filter where

Date Slicer Limiter = IF ( MAX ( Dates[Date] ) <= [prevmonth], 1, 0 )

 

Works perfectly thanks so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors