Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I'm using the following formula to calculate working dates between two dates. It works great if the PickedDate comes after Delivery date. However, the formula returns a 0 instead of the negative value, which is expected, when the PickedDate comes before the DeliveryDate. As the result, the DeliveryDaysVariance is always positive. Please help me create a formula that will generate either positive or negative variance depending whether the PickedDate comes before or after the DeliveryDate. I need to count only working days, so I created a the IsWorkDay column in my RollingDate table that mark working days as 1.
DeliveryDaysVariance = CALCULATE(SUM(RollingDate[IsWorkDay]), DATESBETWEEN(RollingDate[Date], Invoices[DeliveryDate], Invoices[PickedDate])) + 0
Thank you!
Solved! Go to Solution.
Hi,
Try if the following codes work for you...
DeliveryDaysVariance = CALCULATE ( SUM ( RollingDate[IsWorkDay] ), DATESBETWEEN ( RollingDate[Date], MIN(Invoices[DeliveryDate], Invoices[PickedDate]),MAX(Invoices[DeliveryDate], Invoices[PickedDate]) ) ) + 0
Hi,
Try if the following codes work for you...
DeliveryDaysVariance = CALCULATE ( SUM ( RollingDate[IsWorkDay] ), DATESBETWEEN ( RollingDate[Date], MIN(Invoices[DeliveryDate], Invoices[PickedDate]),MAX(Invoices[DeliveryDate], Invoices[PickedDate]) ) ) + 0
Thanks for your response. That's an iteresting solution but it doens't indicate negative variances when the PickDate is prior to DeliveryDate. Your code returns only positive variances. I just wrote the following code which seems to do the job. I was hoping there was a more elegant way of achieving the same result:
DeliveryDaysVariance = IF(Invoices[PickedDate]>Invoices[DeliveryDate] ,CALCULATE(SUM(RollingDate[IsWorkDay]), DATESBETWEEN(RollingDate[Date], Invoices[DeliveryDate], Invoices[PickedDate])) + 0 ,-1*CALCULATE(SUM(RollingDate[IsWorkDay]), DATESBETWEEN(RollingDate[Date], Invoices[PickedDate], Invoices[DeliveryDate])) + 0)
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |