Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Preparing for a certification exam? Ask exam experts all your questions on May 15th. 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!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
77 | |
76 | |
69 | |
48 | |
40 |
User | Count |
---|---|
62 | |
41 | |
33 | |
30 | |
30 |