Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
4 years ago

Datediff return blank value

Dear all,

 

could some help me how to use Datediff function so that in my table I can view all projects and if ITG4 actual date is blank then return a blank row for that measure? please

 

as you can see I have a lot of rows with ITG4 actual being BLANK but when I am adding PVoT to the tabel then I lose all these projects with ITG4 actual BLANK. how can i keep them in the table?

Need Help

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    jalaomar Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • jalaomar's avatar
      jalaomar
      Helper IV
      4 years ago

      Hi Greg_Deckler ,

       

      Thanks for your feedback

       

      so I have these two columns ITG4 baseline & ITG4 actual dates and the following measure is calculating the date difference between these two dates.

       

      PVoT Days = DATEDIFF('KPI'[ITG4 Baseline Data],'KPI'[ITG4 Actual Date],DAY)
       
      It is working OK, but when I add the measure to the table visual, I only see the Projects that have values in the ITG4 baseline & ITG4 actual. see pic below.
      what I want instead is to view all the Projects in the tables visual even for those cases where the ITG4 actual is BLANK. Is there a way to visualize all these projects when I add the measure to the table visual ?
       
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        jalaomar Try:

        PVoT Days = 
  VAR __Diff = DATEDIFF('KPI'[ITG4 Baseline Data],'KPI'[ITG4 Actual Date],DAY)
RETURN
  __Diff + 0
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, jalaomar 

     

    You can try:

    PVoT Days =
VAR __Diff =
    DATEDIFF ( 'KPI'[ITG4 Baseline Data], 'KPI'[ITG4 Actual Date], DAY )
RETURN
    IF ( SELECTEDVALUE ( 'KPI'[ITG4 Actual Date] ) = BLANK (), 0//blank(), __Diff )

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

    • jalaomar's avatar
      jalaomar
      Helper IV
      4 years ago

      Hi, 

       

      I am still getting the same output as previously. Not sure what I am doing wrong...

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        Hi, jalaomar 

         

        First, the expansion of the date column is due to the relationship.  Because of your one-to-many relationship, all possibilities appear when the measure has a value... Unless the column of the date table doesn't appear in visual.

        Secondly, //blank() is the meaning of a comment, which means if 0 doesn't work, you can replace it with blank().

        But if you write it together, you need a newline, otherwise the following , __diff will be commented out...

         

        Janey