Count data between days

[vnqtrang]

Hi,

I have a list of snapshots and the created dates. I would like to have the list (to export) and total of snapshots that were created between > 5d - 10d, >10d - 30d, >30d - 3months, >3 months.

created	snapshot
> 5d - 10d	20
>10d - 30d	40
>30d - 3months	10
>3 months.	4

Thank you in advance.

Tg

[v-jingzhang · Accepted solution]

Hi @vnqtrang 

Assume you want to calculate the days between created date and today, you can add a new column similar to below column. Then add a table visual to the report, add Created and Snapshot ID columns to it and select Count aggregation on Snapshot ID. 

Created = 
VAR _days = DATEDIFF('Table'[Created Date], TODAY(), DAY)
RETURN
SWITCH(TRUE(),
_days<=5, "<5d",
_days<=10, ">5d - 10d",
_days<=30, ">10d - 30d",
">30d")

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @vnqtrang 

Assume you want to calculate the days between created date and today, you can add a new column similar to below column. Then add a table visual to the report, add Created and Snapshot ID columns to it and select Count aggregation on Snapshot ID. 

Created = 
VAR _days = DATEDIFF('Table'[Created Date], TODAY(), DAY)
RETURN
SWITCH(TRUE(),
_days<=5, "<5d",
_days<=10, ">5d - 10d",
_days<=30, ">10d - 30d",
">30d")

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[vnqtrang]

HI, 

Is it possible to filter the option in order of time: 

<=5d
> 5d - 10d
>10d - 30d
>30d - 3months
>3 months.

Because the option "> 5d - 10d" is at the end, i should be below <=5d

Tg

[v-jingzhang · Accepted solution]

Hi @vnqtrang 

Enter a table with Index column. Then sort Created by Index. Sort one column by another column in Power BI - Power BI | Microsoft Docs

Create a relationship between tables on Created column. Then use SortTable's Created column in the table visual instead. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[vnqtrang]

Thank you  it works as I wish.

[ryan_mayu]

@vnqtrang 

could you pls provide the sample data?