Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sheap069
Helper III
Helper III

Make Tables Based on Past 30,90,365 Days

Hello,

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

application idstatusapp typereceived viacountrydecision date
1refusedAmailcanada2021-05-27
2approvedBonlineFrance2022-05-15
3refusedCmailAustralia2021-04-10
4refusedCmailUS2021-04-11
5approvedAmailUS2021-04-12
6withdrawnBonlinecanada2021-04-13
7withdrawnConlinecanada2019-06-02
8withdrawnCmailAustralia2022-05-23
9approvedDonlineAustralia2022-05-24
10approvedAonlineFrance2022-05-25
11refusedAonlineAustralia2022-05-26
12refusedDmailIndia2022-03-12
13withdrawnDmailIndia2022-03-13
14approvedCmailcanada2022-03-14
15withdrawnDmailIndia2022-03-15
16approvedBonlineIndia2021-01-01
17refusedBonlineAustralia2021-01-02
18refusedBmailFrance2020-07-20
19withdrawnCmailUS2020-06-12
20approvedAonlineUS2020-06-13

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

 

# of appspast 30 dayspast 90 dayspast 365 days

 

statuspast 30 dayspast 90 dayspast 365 days
approved# of apps  
refused   

 

typepast 30 dayspast 90 dayspast 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

1 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))
vchenwuzmsft_0-1654069065632.gif

 

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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:

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))
vchenwuzmsft_0-1654069065632.gif

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.