Dynamic Date Measure

DAX DAX Calculations
1

Forum.pbix (135.3 KB)

Hi,

Struggling with the “days overdue” measure in attached.

Depending on what date is selected via slicer, i would expect the days overdue to change but something is not correct. Any ideas?

Cheers

2

@Hitman,

Because of the way Power BI handles dates, you need to use the DATEDIFF function here:

Days Overdue = 
VAR Diff =
DATEDIFF(
    SELECTEDVALUE( 'FACT'[Date Invoiced] ),
    SELECTEDVALUE('FACT'[DUE]),
    DAY
)

Return 
IF(Diff<0,0,Diff)

It was a little unclear to me what you’re trying to do with the slicer, since the in your data model, the active relationship is between Date Table[Date] and Invoice Date. Wasn’t sure whether you were trying to just narrow the range of record shown in the visual, or do a sensitivity analysis on what would happen if you changed either the invoice date or the due date.

Hope this is helpful. Please let me know if you still have questions. Solution file attached below.

3

Thanks Bryan but i don’t think that is looking right (prob on my side due to not explaining clearly)

The days overdue should show for each transaction ID:

  • Difference between the selected date and the date due. E.g if for a trans ID, the selected date via slicer was 10/6/20 and the DUE date was 8/6/20 then it is overdue 2 days.
  • On a side point i have made my “date due” a calculated column but if you are able to do this also via a measure that would be fantastic!

The report (Just a test version), should allow the user to change the selected date which will then change all the days outstanding

Is a that a little clearer?

Thanks,

Andrew

4

@Hitman,

Thanks for the clarification. See if this works for you - here’s what I did:

  1. Created a disconnected table for the slicer using VALUES(Date Table)
  2. Made the slicer a hierarchy, just to minimize the amount of scrolling
  3. Converted the Due Date column to a measure:

Due Date =

SELECTEDVALUE( 'FACT'[Date Invoiced] ) + 
LOOKUPVALUE( 
    Customer[Payment Days],
    Customer[Customer ID],
    SELECTEDVALUE( 'FACT'[Customer ID] )
)

  1. Revised Days Overdue measure per your requirements:

    Days Overdue =

    VAR Diff =
DATEDIFF(
    [Due Date],
    [Selected Date],
    DAY
)

VAR Result =
IF( Diff < 0, 0, Diff )

RETURN Result

  2. Threw in a couple of conditional formatting measures as a bonus…

Here’s what it looks like all pulled together:

image
image1008×619 47.9 KB

Hope this is helpful. Revised solution file posted below.

1 Like
5

Hi Brian, Lovin the response thank you. I am still going through it but wanted to let you know and will come back shortly. BtW above and beyond with the conditional formatting - Thank you sir…

1 Like
6

Hi Brian,

I have gone through the attached and perhaps one of the things i found most useful was seeing how others (you) approach the issue. I must be honest that i would not have thought of the disconnected date table so that was a massive bonus. I’m working through a few other issues so likely i will back asking questions soon but thanks again. Will mark as question solved…

Andrew

7

@Hitman,

> perhaps one of the things i found most useful was seeing how others (you) approach the issue

Great – glad to hear that hit the mark. That’s one of the things I’ve found most valuable on the forum - seeing other approaches completely different from the way I would’ve addressed the problem that have led me to much better/more efficient ways of doing things.

Re: the disconnected table/slicer approach. That’s one of my favorite techniques. Often the same result can be achieved via editing visual interactions, but I find the former approach a lot more direct and transparent. Unless you have the edit interactions feature turned on, you have no idea it’s operating on the visuals, whereas the disconnected table shows up in the measures, the field list and the data model view making it very easy to see exactly what’s going on.

  • Brian