Forum Discussion

kumsha1's avatar
kumsha1
Post Patron
5 years ago
Solved

DATEDIFF between DATE column and SELECTEDVALUE(DATE)

Hi,

I am trying to create a calculated column to show DATEDIFF between a DATE column and SELECTEDVALUE(DATE), but the values are not showing correct. TIA for your help. 

 

DATEDIFF = DATEDIFF(SHIPPING[DISCHARGE_COMMENCE],MAX('DATE'[Date]),DAY)

 

 

Need Help
  • kumsha1's avatar
    kumsha1
    5 years ago

    Sumx(filter(Values(SHIPPING[DISCHARGE_COMMENCE]),[diff] >=0),[SUM of TA_WEIGHTOMETER])

     

    Above formula worked for my requirement, thanks amitchandak  for your help on this.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    kumsha1 , You can not use slicer value in a calculated column. You need to use measure

    DATEDIFF = DATEDIFF(max(SHIPPING[DISCHARGE_COMMENCE]),MAX('DATE'[Date]),DAY)

     

    force a row context in the calculation to get the correct Avg and sum

    example

    Sumx(Values(SHIPPING[ID]),DATEDIFF(max(SHIPPING[DISCHARGE_COMMENCE]),MAX('DATE'[Date]),DAY))

     

    refer by blog too for row context in date diff -

    https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

    • kumsha1's avatar
      kumsha1
      Post Patron
      5 years ago

      Hi amitchandak , thanks for the formula. How can i create a calculate measure for SUM of TA_WEIGHTOMETER below where DATEDIFF >=0...TIA for your help !

       

       

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    kumsha1 , Try measures like

    SUM of TA_WEIGHTOMETER = sum(SHIPPING[TA_WEIGHTOMETER])


    diff =DATEDIFF(max(SHIPPING[DISCHARGE_COMMENCE]),MAX('DATE'[Date]),DAY)


    Sumx(filter(Values(SHIPPING[Shipping No]),[diff] >=0),[SUM of TA_WEIGHTOMETER])

    • kumsha1's avatar
      kumsha1
      Post Patron
      5 years ago

      Hi amitchandak , Any further help is much appreciated.

       

      Created WAG 2 measure but it's not calculating the correct values. I think its due to DATEDIFF measure being considering MAX dates. Below data without actual DATES in the table.

       

       

      Expected results should be as below

       

       

      • kumsha1's avatar
        kumsha1
        Post Patron
        5 years ago

        Sumx(filter(Values(SHIPPING[DISCHARGE_COMMENCE]),[diff] >=0),[SUM of TA_WEIGHTOMETER])

         

        Above formula worked for my requirement, thanks amitchandak  for your help on this.