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...
Need Help
Tips and Tricks
Tutorial Requests
Surendra_thota's avatar
Surendra_thota
Helper II
9 years ago

Hi Dale 

 

Yes , i am using user input date slicer to calculate datediff in calculated column , is it not possible ?  we are able to get a measure by taking max date from selected date and then when i am using that in another table datediff calculation it is not giving expected results , the measure value is taking max value of the date dimension used for slicer instaed of slected date .

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

Surendra_thota

 

Hi,

 

Yes, that's impossible by far. A calculated column is one part of a table. A slicer works in the report. If the slicer can change the value of part of a table, that would be a big mess. Maybe there will be a solution in the future. 

 

I can't reproduce your scenario. Could you please post your formulas and a picture of the wrong result here?

 

Best Regards!

Dale

  • Surendra_thota's avatar
    Surendra_thota
    Helper II
    9 years ago

    Hi Dale 

    My use case is simple we are genereating a report whihc contains calculated column called due dates , that duedates column is calculated  

     

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

     

    So the Days Due calculated column give  number days either +/1  with reference to Today()

     

    User asked to  to put a paarmeter instaed of Today()   so that they can give refernce date instead of today .

     

    what we planned  here to achieve this is we are taking   seperate datetime table where the user can  select data and we are  creating measure to take the selectdate and we want to use that measure in Days due calculated column .

     

    Problem here is we are getting result as the refernce date as max value in that new date dimension instaed os what user is selected .

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Agree, calculated column is just NOT going to help you.  And while it's possible to write the DAX for this in a measure (which is what you should do), I get the feeling that your current data model will make that needlessly complex.  I say that based on your statement that the 2 tables in question are not related.

     

    In general, you are going to be looking to use SUMX to interate over the rows.  Something like:

     

    Selected Date = MAX(Calendar[Date])

    Total Days Due  = SUMX(View_Compliance, View_Compliance[Next Reading Due By] - [Selected Date])