Calculating date difference between selected month in slicer and duedate column

[nathanbidevon]

Hi, 

I have a query that lists the creditors accounts & the due dates. Within my model I have a DimDates table. 

I am trying to work out how to create a measure that would calculate the difference between a selected MONTH from the DimDates table and the Due Date. 

So for example :

ABC has a due date of 31/01/21 for £300. 

If Dec20 was selected, this would show as 31 days. 

Once I have this information I would need to put in a line graph to show the Total owed and across the X axis I would display 30 days, 60 days, 90 days and 120 days to show the totals due from the date selected.

Is this possible? I appreciate any help with this, & thank you very much.

Is this possible? 

[amitchandak]

@nathanbidevon , You can take diff from selected in a measure. But you need force row context for a measure

example

Diff = Datediff(Selectedvalue(Datesdim[Date]), max(Table[DueDate]), Day)

// Put a context of line or account based on need

Avg Days = AverageX(Values(Table[Account ID]), [Diff])

For bucket on this, you need dynamic segmentation using an independent table

https://www.youtube.com/watch?v=CuczXPj0N-k

[nathanbidevon]

Hi @amitchandak Thank you very much for your speedy response. However I still can't get it to work, both measures aren't producing any values. I can't send over the data as it's company confidental however I can provide the headers for the tables. 

Creditors : CustomerName, AccountNumber, TransactionDate, DueDate, InvoiceNo, Gross. 

Dim Dates - Date, Month Name

The only values I need is Due Date and Gross. Would I first need to calculate the last day of the month for the selectable filter of 'Month Name' to calculate the difference between that and the 'Due Date' column in creditors correctly?

[v-yingjl · Accepted solution]

Hi @nathanbidevon ,

Based on your description, you can create a measure like this to calculated the diff when selecting month name in the slicer:

diff = 
DATEDIFF (
    ENDOFMONTH ( 'Dim Dates'[Date] ),
    SELECTEDVALUE ( Creditors[DueDate] ),
    DAY
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.