Datediff return blank value

[jalaomar]

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?

[v-janeyg-msft]

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]

Hi, 

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

[v-janeyg-msft]

Hello @jalaomar 

Try:

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

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

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

[Greg_Deckler]

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

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]

@jalaomar Try:

PVoT Days = 
  VAR __Diff = DATEDIFF('KPI'[ITG4 Baseline Data],'KPI'[ITG4 Actual Date],DAY)
RETURN
  __Diff + 0

[jalaomar]

Hi Greg

I tried out the measure and it didn't exactly give me the results I was looking for 

so below pic is all the data in the table visual and the second visual is when I include the measure to the table visual