Forum Discussion

Surendra_thota's avatar
Surendra_thota
Helper II
9 years ago

DateDiff Days giving incorrect result

Hi All

 

we need to calculate number days between  [next reading due date]  and Report genertion date  (  Instead of Today () , we are taking a measure from Date dimenion to get max input date from filter  which is constant across all rows), i am attaching the screen shot , please let us know where we are going wrong .

Need Help
Tips and Tricks
Tutorial Requests

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    That is super weird, and I am mostly commenting just so I can follow along :D

    My only thought would be to try to wrap CALCULATE( )  around your call to DATEDIFF.

    • Surendra_thota's avatar
      Surendra_thota
      Helper II
      9 years ago

      Hi Scottsen

      when i use today() function i am getting correct value 

      Days Due  = View_Compliance[Next Reading Due By]- Today()

       

      but when i want use a constant date ie selected from input slicer , it is nota accepting , even i tried to give max reportiondate as measure  as well directly as shown below but bothcases it is taking only last value of the genetation date table value but not slected value.

       

      Days Due Column = View_Compliance[Next Reading Due By]- MAX('Report Generation Date Table'[Report Generation Date])

       

      How to pass a slected date value from slicer used in difffrent table as variable

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        * I'm curious what the relationship is between View_Compliance and 'Report Generation Date Table' tables.   I didn't catch before these were from different tables, and you might just need to use the RELATED( ) function for 1 of your columns.

         

        * You mention a "slicer" -- note those will NOT impact a calculated column in any way.  Only measures are impacted by slicers.  Likely you can write a "Total Difference" measure that aggregates the difference from each line.

         

         

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Surendra_thota

     

    Hi,

     

    Could you provide a little sample please? It's possible in your situation. Consider this: there should be one column which would be the primary column ( making the row unique). So there is only one [Next Reading Due By]. We can create a MEASURE like this:

     

    Days =
IF (
    MIN ( view_compliance[next reading due by] )
        < MAX ( 'Report Generation Date Table'[ReportGeneration] ),
    DATEDIFF (
        MIN ( view_compliance[Next Reading Due by] ),
        MAX ( 'Report Generation Date Table'[ReportGeneration] ),
        DAY
    ),
    0
)

    The report would like this with a slicer of [ReportGeneration]:

     

    TheUniqueColumn   [Next Reading Due by]   Days

     

    Please have a try.

    Best Regards!

    Dale

    • Surendra_thota's avatar
      Surendra_thota
      Helper II
      9 years ago

      Hi Dale 

       

      I have tried as you said , atleast now we are not getting any issue but not giving correct answer ,  please see the attached screenshot , i took the report generation date as 1st jan 2017 ,  

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Surendra_thota

         

        Hi,

         

        The result is correct in some way. It seems that you used the formula as a calculated column. So "MAX ( 'Report Generation Date Table'[ReportGeneration] )" is always the same. It's "2018-12-31" in your table. You have to use it as a measure if you want it to respond to the slicer. Finally, create a report with the measure. Please have a try. 

         

        Best Regards!

        Dale