Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
I have 2 columns
one vRTT[DateInPublishing
vRTT[DateOutOfPublishing]
If vRTT[DateOutOfPublishing] ISBLANK I would like DateDiff to calculate based on TODAY() otherwise it would calculate the datediff between the In and Out fields.
I thought something like the following woud work...
WIPLayout = DATEDIFF(vRTT[DateInPublishing],
(IF(ISBLANK(vRTT[DateOutOfPublishing]), TODAY(), vRTT[DateOutOfPublishing])),
vRTT[DateOutOfPublishing],DAY)
Solved! Go to Solution.
Okay I think we got this - test it to make sure
WIPLayout = IF ( ISBLANK ( vRTT[DateOutOfPublishing] ), SWITCH ( TRUE (), TODAY () > vRTT[DateInPublishing], DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ), TODAY () < vRTT[DateInPublishing], DATEDIFF ( TODAY (), vRTT[DateInPublishing], DAY ) * -1, 0 ), SWITCH ( TRUE (), vRTT[DateOutOfPublishing] > vRTT[DateInPublishing], DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY ), vRTT[DateOutOfPublishing] < vRTT[DateInPublishing], DATEDIFF ( vRTT[DateOutOfPublishing], vRTT[DateInPublishing], DAY ) * -1, 0 ) )
EDIT:
So if the "OutOf" or "published" date is blank
The first SWITCH checks whether the "In" or "submitted" date is so to speak in the future i.e. > today
this is what throws off the error (those results will be negative * -1)
The second SWITCH checks whether it could have been "published" before it was ever "submitted"
again those results will be negative
Hope this makes sense!
WIPLayout = IF ( ISBLANK ( vRTT[DateOutOfPublishing] ), DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ), DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY ) )
Supplental question:
WIPapproval = DATEDIFF(vRTT[DateOutOfPublishing], (IF(ISBLANK(vRTT[PublishedDate]), TODAY(), vRTT[PublishedDate])), DAY)
returns an error:
In DATEDIFF function, the start date cannot be greater than the end date
How would I put an IF statement in front to capture the situations where the DATEDIFF throws the error?
Okay I think we got this - test it to make sure
WIPLayout = IF ( ISBLANK ( vRTT[DateOutOfPublishing] ), SWITCH ( TRUE (), TODAY () > vRTT[DateInPublishing], DATEDIFF ( vRTT[DateInPublishing], TODAY (), DAY ), TODAY () < vRTT[DateInPublishing], DATEDIFF ( TODAY (), vRTT[DateInPublishing], DAY ) * -1, 0 ), SWITCH ( TRUE (), vRTT[DateOutOfPublishing] > vRTT[DateInPublishing], DATEDIFF ( vRTT[DateInPublishing], vRTT[DateOutOfPublishing], DAY ), vRTT[DateOutOfPublishing] < vRTT[DateInPublishing], DATEDIFF ( vRTT[DateOutOfPublishing], vRTT[DateInPublishing], DAY ) * -1, 0 ) )
EDIT:
So if the "OutOf" or "published" date is blank
The first SWITCH checks whether the "In" or "submitted" date is so to speak in the future i.e. > today
this is what throws off the error (those results will be negative * -1)
The second SWITCH checks whether it could have been "published" before it was ever "submitted"
again those results will be negative
Hope this makes sense!
Thank you very much!