cancel
Showing results for 
Search instead for 
Did you mean: 
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.



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors