Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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....
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |