Add a custom column to track the date

[MichaelJackpbi]

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.

[AlexChen · Accepted 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

[CahabaData]

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....