Forum Discussion

ganchevd's avatar
ganchevd
Icon for Helper I rankHelper I
8 years ago
Solved

Difference between due date and selected calendar date

Hello,

 

I have an issue by making the calendar slicer affect the days overdue. From the table bellow:

 

Customer nameCust No.Document dateDocument No.:Due dateInvoicedEnding balance
BATHBASH20439867804.12.201710001283014.12.2017341.78341.78
MENIN2501401.03.201810001330831.03.2018600.00600.00
ASYAS20251021011.04.201810001352514.04.2018126.00126.00
LOANGROUP20508824803.05.201810001359806.05.201884.0084.00
CENTURY20459176602.05.201810001358912.05.20181 144.921 144.92
OK CORP10176716502.05.201810001358412.05.2018120.00120.00

 

I'd like to add another column called "Days Overdue", which to return the number of overdue days, based on the selected date from my calendar table. I've tried som variations of DATEDIFF but neither worked. I have a separate calendar table, linked with the DataTable, One to Many.

 

I would appreciate it very much if you help me with this issue. I think I am very close but not exactly.

Need Help
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    You would need to create a measure, which should be something like:

     

    Measure = 
VAR MaxDate = MAX('Calendar'[Date]) // Or MAXX(ALLSELECTED('Calendar'),[Date])
VAR DueDate = MAX('Table'[Date])
RETURN DATEDIFF(MaxDate,DueDate,DAY)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    You would need to create a measure, which should be something like:

     

    Measure = 
VAR MaxDate = MAX('Calendar'[Date]) // Or MAXX(ALLSELECTED('Calendar'),[Date])
VAR DueDate = MAX('Table'[Date])
RETURN DATEDIFF(MaxDate,DueDate,DAY)
    • ganchevd's avatar
      ganchevd
      Icon for Helper I rankHelper I
      8 years ago

      Thank you. It is working. It changes dates but when I try to add the column into the table above it breaks. Probably something with the link between calendar table and the data table. Is there any difference if the Calendar Table is prepared in Power Query Mode? Is there a way to do it as Calculated column. Since the data is huge and it is refreshing very slowly. I tried to put the same formula in Column, but it did not work properly. I received one and the same value for each of the rows.

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        ganchevd,

         

        Values in a calculated column are fixed. They are an immutable result for each row in the table.