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 D...
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])





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])





autodafe's avatar
autodafe
Resolver I
7 years ago

Here's what I got for a specific date (18/08/2018)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Worked very well. Thanks .

     

    Can you help me exclude weekends and holidays now ? 

    Thanks .

     

    • autodafe's avatar
      autodafe
      Resolver I
      7 years ago

      try to create a separate Date dimension, add one ro more attribute (true/false) for holidays and weekends, somethin like

      Date                       Weekend                      Holiday
01/05/2019                 False                        True

02/05/2019                 False                        False

03/05/2019                 False                        False

04/05/2019                 True                         False

05/05/2019                 True                         False


      the Weekend column could be calculated using the WEEKDAY function (if weekday is Sat or Sun  then  TRUE)
      https://docs.microsoft.com/en-us/dax/weekday-function-dax