cancel
Showing results 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

Frequent Visitor

## Help needed Past before 30, 60 or 90 days date period

Hi guys,
I'm new to Dax & PowerBI calculations. I have a Auto Calendar that automaticly create a range of dates for me. I made a table (Period) where i can select with a slicer 30, 60, or 90 days in the past.

Period =
VAR
CALCULATETABLE'Calendar' ,DATESBETWEEN'Calendar'[Date]TODAY() - 30TODAY() ) "In The Last" , "30 Days")

VAR
CALCULATETABLE'Calendar' ,DATESBETWEEN'Calendar'[Date]TODAY() - 60TODAY() ) "In The Last" , "60 Days")

VAR
CALCULATETABLE'Calendar' ,DATESBETWEEN'Calendar'[Date]TODAY() - 90TODAY() ) "In The Last" , "90 Days")

RETURN
UNION(_last30days,_last60days,_last90days)

But I want to select all the dates past before the 30, 60 or 90 days? without including the 30, 60, or 90 days in the selection. Any Ideas?

1 ACCEPTED SOLUTION
Frequent Visitor

With alil bit more research i found the solutions to my problems..

Basically going back to my roots and went ahead to research and read command syntax until i got a good example.

Solutions :

Period =

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN( 'Calendar'[Date], TODAY() - 30, TODAY() )
)
, "In The Last" , "30 Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date], BLANK(), TODAY() - 30 )
)
, "In The Last" , "30+ Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN( 'Calendar'[Date], TODAY() - 60, TODAY() )
)
, "In The Last" , "60 Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date], BLANK(), TODAY() - 60 )
)
, "In The Last" , "60+ Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN( 'Calendar'[Date], TODAY() - 90, TODAY() )
)
, "In The Last" , "90 Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date], BLANK(), TODAY() - 90 )
)
, "In The Last" , "90+ Days"
)

RETURN
UNION(_last30days, _last30plusdays, _last60days, _last60plusdays, _last90days, _last90plusdays)

3 REPLIES 3
Frequent Visitor

With alil bit more research i found the solutions to my problems..

Basically going back to my roots and went ahead to research and read command syntax until i got a good example.

Solutions :

Period =

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN( 'Calendar'[Date], TODAY() - 30, TODAY() )
)
, "In The Last" , "30 Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date], BLANK(), TODAY() - 30 )
)
, "In The Last" , "30+ Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN( 'Calendar'[Date], TODAY() - 60, TODAY() )
)
, "In The Last" , "60 Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date], BLANK(), TODAY() - 60 )
)
, "In The Last" , "60+ Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN( 'Calendar'[Date], TODAY() - 90, TODAY() )
)
, "In The Last" , "90 Days"
)

VAR
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date], BLANK(), TODAY() - 90 )
)
, "In The Last" , "90+ Days"
)

RETURN
UNION(_last30days, _last30plusdays, _last60days, _last60plusdays, _last90days, _last90plusdays)

Super User

@jelibier , You need an independent date table and a table with values 30, 60, 90 , it can another column with 30 days , 60 days for display

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max - selectedvalue(slicer[Days]) // - maxx(allselected(slicer), [Days])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-3...

Frequent Visitor

Hi @amitchandak,
Thank you for responding to my request,
The answer you gave me is for me to create a table for the last 30, 60 or 90 days etc..., Wat i am asking is if its possible to select the values after those days from today dynamically..

Explanation:
I have an CalendarAuto table ("Calendar").

the [Date] in the "Calendar" table goes as follows From (8/17/2022) to (1/1/2007).
I have a Period Table ("Period") In which i can select the past 30, 60, 90 or etc... days from Today().
(I created this using this person video (https://www.youtube.com/watch?v=hDopw1mPlrU ).
What i wanted was the same as i can select the past 30, 60, 90 or etc days from Today(), is select the dates past the 30, 60 or 90 days without including the 30, 60, or 90 days in the selection.

Scenario:
I have 1 complete year of Users logon activities and after 1 year people came and left and i want to see after 1 year how many people i have activelly every 30 days, 60 days or even 90 days From today.
Lets say in total i have 100 Users..
With the "Period" table i created i can use it in combination with a slicer to see how many people i have active every 30 days, 60, days or even 90 days.

Lets say i select 30 Days. and i see in the last 30 days i had 50 Users active...
I selecet 60 Days i see, i had 60 people active...
I use Drill throught so i can see in a list view all my active users Username.
it shows me all 60 users Usernames who were active these past 60 days.
i want to elimate those 40 inactive users so i dont have to many clutter in my network.

so what i want is to create a periode in which i start past 60 days from today to the last date in my CalenderAuto.

Example:
(Past 30 Days From TODAY() (8/17/2022)) to (1/1/2007)
(Past 60 Days From TODAY() (8/17/2022)) to (1/1/2007)
(Past etc Days From TODAY() (8/17/2022)) to ...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors