Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Add a custom column to track the date

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.

1 ACCEPTED SOLUTION
AlexChen
Employee
Employee

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")))

 

1.png

 

Best Regards

Alex

 

View solution in original post

2 REPLIES 2
AlexChen
Employee
Employee

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")))

 

1.png

 

Best Regards

Alex

 

CahabaData
Memorable Member
Memorable Member

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

 

www.CahabaData.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.