Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Hours |
9/5/2021 | 36 |
10/7/2018 | 35 |
10/14/2018 | 26 |
Punch_Report
DATE | TOTAL |
9/25/2021 | 1.5 |
9/24/2021 | 5.32 |
9/23/2021 | 5.82 |
Date Table Sample
Date | Month & Year | DateInt |
8/3/2021 | Aug-21 | 20210803 |
9/7/2021 | Sep-21 | 20210907 |
10/5/2021 | Oct-21 | 20211005 |
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!
Solved! Go to 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 )
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.
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
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!
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |