Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have the following data, as can be seen here.
application id | status | app type | received via | country | decision date |
1 | refused | A | canada | 2021-05-27 | |
2 | approved | B | online | France | 2022-05-15 |
3 | refused | C | Australia | 2021-04-10 | |
4 | refused | C | US | 2021-04-11 | |
5 | approved | A | US | 2021-04-12 | |
6 | withdrawn | B | online | canada | 2021-04-13 |
7 | withdrawn | C | online | canada | 2019-06-02 |
8 | withdrawn | C | 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 | India | 2022-03-12 | |
13 | withdrawn | D | India | 2022-03-13 | |
14 | approved | C | canada | 2022-03-14 | |
15 | withdrawn | D | India | 2022-03-15 | |
16 | approved | B | online | India | 2021-01-01 |
17 | refused | B | online | Australia | 2021-01-02 |
18 | refused | B | France | 2020-07-20 | |
19 | withdrawn | C | 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
Solved! Go to Solution.
Hi @sheap069 ,
You can try martix table or table visual to do this.
measures:
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.
@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.
Hi, I've made those measures but how do I make the tables? I don't know how to construct them using this information.
Hi @sheap069 ,
You can try martix table or table visual to do this.
measures:
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |