cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KA95
Helper III
Helper III

Show value from the last 30-90 days from this current date?

Hi there, 

I was just wondering if there is a way I can create a measure that will show a total value for the last 30-90 days? 

So currently I have a table with two columns, an ID column and an date column. I have created a count measure that counts the ID's and it's called [Total Users] then I have a date column that's called [active_date].

How would I be able to get the [Total Users] from the last 30-90 days. I've tried the relative date filter tool but you can't set between conditions that's relative to todays date, so I'm hoping this can be done in a measure? 

1 ACCEPTED SOLUTION
Whitewater100
Super User
Super User

Hi:

Can you add date table and connect to our fact table on Date field? I'll paste new Date Table code below.. It should also be marked as a Date Table. Using the Dates[Date] field in your visuals:

Total Retail last 90 to 30 Days =
var lstdate = LASTDATE('SalesOrders'[Order_Date])
var lstdateless90 = lstdate -90
var lstdateless30 = lstdate -30
return
CALCULATE([Total Users], DATESBETWEEN(Dates[Date], lstdateless90, lstdateless30))
 
Date Table. MODELING> New Table

Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[ Date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

I would format the Dates[Date Field] to a Date like 

Whitewater100_0-1654737367140.png

I hope this helps..

View solution in original post

3 REPLIES 3
Whitewater100
Super User
Super User

Hi:

Can you add date table and connect to our fact table on Date field? I'll paste new Date Table code below.. It should also be marked as a Date Table. Using the Dates[Date] field in your visuals:

Total Retail last 90 to 30 Days =
var lstdate = LASTDATE('SalesOrders'[Order_Date])
var lstdateless90 = lstdate -90
var lstdateless30 = lstdate -30
return
CALCULATE([Total Users], DATESBETWEEN(Dates[Date], lstdateless90, lstdateless30))
 
Date Table. MODELING> New Table

Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[ Date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

I would format the Dates[Date Field] to a Date like 

Whitewater100_0-1654737367140.png

I hope this helps..

Thank you @Whitewater100 

That worked brilliantly! 

Great! Your welcome..

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.