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 Friends,
I have a exel file including itemcode,expireddate. Now i need to add a new column naming Expired_Days to calculate the number of days from today to the expired date for every item. But when i use the formula:
Expired_Days = DATESBETWEEN(Sheet1[Expire Date],Sheet1[Expire Date],TODAY())
or
Expired_Days = DATEDIFF(Sheet1[Expire Date].[Date],TODAY(),DAY)
i got an error as the startdate cannot be greater than enddate, because in expired date field containing both the date that > today() and the date <TOday()..
I hope to create a report to divide the number of expired days by 3 ranks like <30 days, 30 days<&& <60 days,>60 days...depend on the Expired_Days column by using the formula :
IF(Expired_DAys<0 then " Exprired"
ELSE IF(Expried_Day) < 30 then "Less than 30"
Else if (Expired_Day >30 and Expired_day<60) then " more than 30 and less than 60
else more than 60
the meeasre is Count(Itemcode)
This is formula i had in Tableau but i dont know how to do that in Power BI, Please help me to resolve it.
Thanks,
Michael.
Solved! Go to Solution.
Hi,
I assume your table called “itemInfo”. You can use following formula to calculate expired days
Expired_Days = value(TODAY()) - value(itemInfo[expireddate])
You can use formula below to calculate its expired status.
Expired_status = if(itemInfo[Expired_DAys] < 0, "Not Expired",
if(itemInfo[Expired_DAys] < 30, "Less than 30",
if(itemInfo[Expired_DAys] >= 30 && itemInfo[Expired_DAys] < 60, " more than 30 and less than 60",
"more than 60")))
Best Regards
Alex
Hi,
I assume your table called “itemInfo”. You can use following formula to calculate expired days
Expired_Days = value(TODAY()) - value(itemInfo[expireddate])
You can use formula below to calculate its expired status.
Expired_status = if(itemInfo[Expired_DAys] < 0, "Not Expired",
if(itemInfo[Expired_DAys] < 30, "Less than 30",
if(itemInfo[Expired_DAys] >= 30 && itemInfo[Expired_DAys] < 60, " more than 30 and less than 60",
"more than 60")))
Best Regards
Alex
Your key DAX is:
DayCountToday = DATEDIFF([ExpDate],TODAY(),DAY)
this works if Exp Date is earlier than today....
one would reverse it if Exp Date is later than today:
DayCountToday = DATEDIFF(TODAY(),[ExpDate],DAY)
so you might wrap it with an IF to determine the relationship between dates and what you want your default output...such as:
STATUS = IF( [ExpDate]>Today(), <formula>, 0 )
this would fire the formula only if the Exp Date is greater than today - otherwise it will output 0 which then I think intend to band as 'expired'.....
something along these lines I think....
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |