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
rrhutch
Helper II
Helper II

Problem Getting Measure to Give Closest Date Without Exceeding

I have a table of data like the following:

 

APPIDSIDYRTRMPROGRAMDEGREESTATUSDATE
1111112019 FallUndergradBSPending1/8/19
1111112019 FallUndergradBSAccepted1/31/19
1111112019 FallUndergradBSDeferred4/15/19
2222222019 FallGradMDivPending12/1/18
2222222019 FallGradMDivAccepted2/10/19
2222222019 FallGradMDivDeferred3/17/19
3222222020 SpringGradMDivAccepted3/17/19
4222232019 FallGradMAPending3/1/19
4222232019 FallGradMAPending3/15/19
4222232019 FallGradMAAccepted5/1/19
5555672019 FallAdvPhDPending2/9/19
5555672019 FallAdvPhDPending3/1/19
5555672019 FallAdvPhDAccepted3/31/19
6111112020 JanuaryUndergradBSAccepted4/15/19

 

What I a trying to do is indentify a record that is the max record less than the max date selected from a disconnected date slicer. In order to accomplish this, I am using the following measure:

 

MyDate =
VAR SlicerDate =
    MAX ( dDate[DATE] )
VAR MinDiff =
    MINX (
        FILTER ( ALL ( 'App Data' ), 'App Data'[APPID] IN VALUES ( 'App Data'[APPID] ) ),
        ABS ( SlicerDate - 'App Data'[DATE] )
    )
RETURN
    MINX (
        FILTER (
            ALL ( 'App Data' ),
            'App Data'[APPID] IN VALUES ( 'App Data'[APPID] )
                && ABS ( SlicerDate - 'App Data'[DATE] ) = MinDiff
        ),
        'App Data'[DATE]
    )

 

The measure is working, but I am not getting a the date return for the record with the date closest, but not exceeding the max date selected. Instead, I am just getting the closet date to the max value in the slicer, even though it might exceed the date.

 

For example, if I the max date I chose from the slicer is 3/22/19, I get the following result:

 

{8FD8E79C-AF47-46E8-8BD7-2F1E327BBD7C}.png.jpg

 

Instead, I am wanting it to return:

 

APPIDSIDYRTRMPROGRAMDEGREESTATUSDATEMyDate
1111112019 FallUndergradBSPending1/8/191/31/19
1111112019 FallUndergradBSAccepted1/31/191/31/19
1111112019 FallUndergradBSDeferred4/15/191/31/19
2222222019 FallGradMDivPending12/1/183/17/19
2222222019 FallGradMDivAccepted2/10/193/17/19
2222222019 FallGradMDivDeferred3/17/193/17/19
3222222020 SpringGradMDivAccepted3/17/193/17/19
4222232019 FallGradMAPending3/1/193/15/19
4222232019 FallGradMAPending3/15/193/15/19
4222232019 FallGradMAAccepted5/1/193/15/19
5555672019 FallAdvPhDPending2/9/193/1/19
5555672019 FallAdvPhDPending3/1/193/1/19
5555672019 FallAdvPhDAccepted3/31/193/1/19
6111112020 JanuaryUndergradBSAccepted4/15/19 

 

I know it is something simple in my measure that I am missing, but hoping some other eyes can help me point out what is wrong.

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

This could be due to the ABS() in the MinDiff returning the same value if the differences is plus or minus x days.

 

I would probably simplify this measure down to something like the following

 

MyDate2 = 
VAR SlicerDate =
    MAX ( dDate[DATE] )
RETURN CALCULATE(max('App Data'[DATE])
  , Filter(all('App Data'[DATE]), 'App Data'[DATE] <= SlicerDate)
)

View solution in original post

1 REPLY 1
d_gosbell
Super User
Super User

This could be due to the ABS() in the MinDiff returning the same value if the differences is plus or minus x days.

 

I would probably simplify this measure down to something like the following

 

MyDate2 = 
VAR SlicerDate =
    MAX ( dDate[DATE] )
RETURN CALCULATE(max('App Data'[DATE])
  , Filter(all('App Data'[DATE]), 'App Data'[DATE] <= SlicerDate)
)

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.