Forum Discussion

b2wise's avatar
b2wise
Icon for Helper III rankHelper III
4 years ago
Solved

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.

 

 

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!

 

Need Help
Need Help DAX
Optimizing DAX
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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 )

     

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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 
    )
    • b2wise's avatar
      b2wise
      Icon for Helper III rankHelper III
      4 years ago

      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.

       

       

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

       

       

      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

       

      • b2wise's avatar
        b2wise
        Icon for Helper III rankHelper III
        4 years ago

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