Make Tables Based on Past 30,90,365 Days

[sheap069]

Hello,

I have the following data, as can be seen here.

application id	status	app type	received via	country	decision date
1	refused	A	mail	canada	2021-05-27
2	approved	B	online	France	2022-05-15
3	refused	C	mail	Australia	2021-04-10
4	refused	C	mail	US	2021-04-11
5	approved	A	mail	US	2021-04-12
6	withdrawn	B	online	canada	2021-04-13
7	withdrawn	C	online	canada	2019-06-02
8	withdrawn	C	mail	Australia	2022-05-23
9	approved	D	online	Australia	2022-05-24
10	approved	A	online	France	2022-05-25
11	refused	A	online	Australia	2022-05-26
12	refused	D	mail	India	2022-03-12
13	withdrawn	D	mail	India	2022-03-13
14	approved	C	mail	canada	2022-03-14
15	withdrawn	D	mail	India	2022-03-15
16	approved	B	online	India	2021-01-01
17	refused	B	online	Australia	2021-01-02
18	refused	B	mail	France	2020-07-20
19	withdrawn	C	mail	US	2020-06-12
20	approved	A	online	US	2020-06-13

I would like to make tables that contain the following fields based on the decision dates

# of apps

past 30 days

past 90 days

past 365 days

status	past 30 days	past 90 days	past 365 days
approved	# of apps
refused

type	past 30 days	past 90 days	past 365 days
A	# of apps
B
C
D

and so on for the rest of the columns. I am unsure of how to approach this problem.

Thank you

[amitchandak]

@sheap069 , You can create measure like

Rolling 30 = CALCULATE(countrows(table) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

Rolling 60 = CALCULATE(countrows(table) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-60,DAY))

Rolling 90 = CALCULATE(countrows(table) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,DAY))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[sheap069]

Hi, I've made those measures but how do I make the tables? I don't know how to construct them using this information. 

[v-chenwuz-msft · Accepted solution]

Hi @sheap069 ,

You can try martix table or table visual to do this.

measures:

past 30 days =

CALCULATE(COUNTROWS('Table'),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,DAY))

past 90 days =

CALCULATE(COUNTROWS('Table'),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-90,DAY))

past 365 days =

CALCULATE(COUNTROWS('Table'),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-365,DAY))

I created a pbix for you to refer. The pbix in the attachment.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.