cancel
Showing results for
Did you mean:
Frequent Visitor

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Champion

Hi J.

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors