Forum Discussion

pvtrinh89's avatar
pvtrinh89
Frequent Visitor
9 years ago
Solved

Calculate days range between dates

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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