topic Re: Show value from the last 30-90 days from this current date? in DAX Commands and Tips

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

Anonymous — Thu, 09 Jun 2022 00:47:38 GMT

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?

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

Whitewater100 — Thu, 09 Jun 2022 01:16:26 GMT

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

I hope this helps..

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

Anonymous — Thu, 09 Jun 2022 01:28:02 GMT

Thank you Whitewater100

That worked brilliantly!

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

Whitewater100 — Thu, 09 Jun 2022 12:12:40 GMT

Great! Your welcome..

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

Anonymous — Thu, 12 Jan 2023 00:32:07 GMT

Hello Whitewater100 , can you please help?

I have created, below filter,

In the marks card I want to display today's date minus what ever the option I pick from the slicer,

So if today is 11 January, 2023 and user pick 'last 6 months' from the slicer then I would like to display,

(11 July, 2022 - 11 January, 2023)

It should keep on changing based on what user pick from the slicer.

How can I do that? Please help!