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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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