Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DATEDIFF HELP

Combo selection column is: Date Demande
 
Quick question, if [Date de présentation à l'entité] is BLANK, I want to DATEDIFF between the Date Demande and the end of the month( Selected in the Date Demande combo)
 
Eg of one row im my table:
REF               Date demande     Date de présentation à l'entité
FP#00106     27/02/2019
 

My expression: 

M_Date_DIFF =
IF(t_Missions[Date de présentation à l'entité] = BLANK ();
DATEDIFF(t_Missions[Date demande]; EOMONTH(MAX(t_Missions[Date demande]);0); DAY);
DATEDIFF(t_Missions[Date demande]; t_Missions[Date de présentation à l'entité]; DAY)
)
Need Help
Show and Tell
Tips and Tricks
  • autodafe's avatar
    autodafe
    7 years ago

    I Think you need MEASURES, not COLUMNS.

    I have tried to replicate your scenario using data1 and data2 as dates, calculate 2 differente DATEDIFF and then a "FINAL" measure with an IF. Not super-elegant but it seems to be working

    www.autodafe.net/tmp/EXAMPLE.pbix

    1st I Calculate (Measure) End of Selected Month:

    EOM = EOMONTH(SELECTEDVALUE(Cal[Date]);0)
     
    then I have 2 differente DATEDIFFs
    DDIFF1 =DATEDIFF(SELECTEDVALUE(Foglio1[data1]);[EOM];DAY)
DDIFF2 = DATEDIFF(SELECTEDVALUE(Foglio1[data1]);SELECTEDVALUE(Foglio1[data2]);DAY)
     
     
    Then a simple IF takes what's needed:
    Final = IF(ISBLANK([DDIFF2]); [DDIFF1];[DDIFF2])





6 Replies

  • autodafe's avatar
    autodafe
    Resolver I
    7 years ago

    so the question is???
    your formula looks correct. Are you creating a new COLUMN or new MEASURE ??

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi autodafe , 

       

      It's a column. 

      Here's the result: 

      .

       

      as you can see it does not calculate until the end of the selected month (Date_Demande) - FP#00057

      Can you help?

      • autodafe's avatar
        autodafe
        Resolver I
        7 years ago

        I Think you need MEASURES, not COLUMNS.

        I have tried to replicate your scenario using data1 and data2 as dates, calculate 2 differente DATEDIFF and then a "FINAL" measure with an IF. Not super-elegant but it seems to be working

        www.autodafe.net/tmp/EXAMPLE.pbix

        1st I Calculate (Measure) End of Selected Month:

        EOM = EOMONTH(SELECTEDVALUE(Cal[Date]);0)
         
        then I have 2 differente DATEDIFFs
        DDIFF1 =DATEDIFF(SELECTEDVALUE(Foglio1[data1]);[EOM];DAY)
DDIFF2 = DATEDIFF(SELECTEDVALUE(Foglio1[data1]);SELECTEDVALUE(Foglio1[data2]);DAY)
         
         
        Then a simple IF takes what's needed:
        Final = IF(ISBLANK([DDIFF2]); [DDIFF1];[DDIFF2])