Calculate days range between dates

[pvtrinh89]

Hi,

I have a table containing the expired date of product, so now i want to crate a report to track the citeria for them. in fact, i need to count how many product that have the number of days till exprired date <30 ||  30<exprired date<60|| 60< exprired date<90 and >90 days..

Could any one give me some advice on that ?

Best regards,
J.

[v-shex-msft · Accepted solution]

Hi @pvtrinh89,

You can follow below steps to get the count of days range:

1. Create a test table.
 

2. Add the measure to calculate the range.

exprired range =
var temp= MAX(Sheet6[exprired date])
var daynumber= DATEDIFF(min(TODAY(),temp),MAX(TODAY(),temp),DAY)
return
if(daynumber <30,"<30",if( AND(daynumber>30,daynumber<60),"30~60",if(AND(daynumber>60,daynumber<90),"60~90",">90")))

3. Add a calculate column to store the “range”.

 

3. Create a new table to get the summarize information.

Table = DISTINCT( SELECTCOLUMNS(Sheet6,"Range",[Range],"Count",COUNTAX(FILTER(Sheet6,[Range]=EARLIER(Sheet6[Range])),[Range])))
 

Regards,
Xiaoxin Sheng

[BhaveshPatel]

Hi J.

Please refer to the following link for more detailed classification approach.

Thanks & Regards,

Bhavesh