Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jelibier
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
    _last30days = ADDCOLUMNS
    CALCULATETABLE'Calendar' ,DATESBETWEEN'Calendar'[Date]TODAY() - 30TODAY() ) "In The Last" , "30 Days")

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

VAR
    _last90days = ADDCOLUMNS(
    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
jelibier
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
    _last30days = ADDCOLUMNS(
    CALCULATETABLE(
        'Calendar'
        ,DATESBETWEEN( 'Calendar'[Date], TODAY() - 30, TODAY() )
    )
    , "In The Last" , "30 Days"
)

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

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

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

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

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

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



View solution in original post

3 REPLIES 3
jelibier
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
    _last30days = ADDCOLUMNS(
    CALCULATETABLE(
        'Calendar'
        ,DATESBETWEEN( 'Calendar'[Date], TODAY() - 30, TODAY() )
    )
    , "In The Last" , "30 Days"
)

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

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

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

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

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

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



amitchandak
Super User
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...

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.

jelibier_0-1660752122208.png

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 ...

 

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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