Grouping the data according to the age.

[Anonymous]

Hi,

I have the data in the following way:

Entry Date          XCategory       YDetail      

1/1/2020               A                  B

2/3/2020               c                   d

ddmmyy                x                    y             and so on.....

I have a date selected from the date table = calendar(dates(ddmmyy),today()) - I am able to select the date from slicer.

I have to calculate the Age of each detail by substracting the entry date and selected date and show the data in the following pivoted way:

0-7days               8-14days               15-22 days ...... so on

count(detail)       count(detail)           count(detail) .... so on

I have acheived till getting the age, but am unable to filter out the data as shown above.

Any help on this??

[Anonymous · Accepted solution]

@Anonymous 

I am afraid you cannot put the days before like the expected result, because it can only be measure if you want it to be dynamic. As the workaround, you can try create 2 following measures. However, the problem is if you want the range range days field you can only input 1 by 1, there is no straight forward feature to generate the range for measures.

Day Diff = DATEDIFF(SUM([Entry Date]),SELECTEDVALUE('Date table'[Date]),DAY)

Days = CALCULATE(SWITCH(TRUE(),
[Day Diff]>=0 && [Day Diff]<=7,"0-7 Days",
[Day Diff]>=8 && [Day Diff]<=14,"8-14 Days",
[Day Diff]>=15 && [Day Diff]<=22,"15-22 Days"))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.