Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |