Filtering relative date ranges

[Anonymous]

Hi All,

Scenario:

I want to create some preset date filters for my report users, but I'm unsure how to filter custom dynamic relative dates.

I've attempted using bookmarks and adding multiple relative date filters as per the below image, but this doesn't work the way I was hoping.

Desired Result:

The user will be able to select from the following options:

• Last Month
• 1-3 Months
• 3-6 Months
• 6-9 Months
• 9-12 Months
• 12 Months+

This will then display the sum of Orders with a Creation Date within that date range, and can then be used as a drillthrough to get the information of Orders created within that time.

Example Result:

Selecting Last 1 - 3 Months would display as below:

Selecting Last 3-6 Months would display as below:

Appreciate any assistance with this. 

[amitchandak]

@Anonymous , You can dow that with an independent date table and independent slicer of these values

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max1 = maxx(allselected(Date1),Date1[Date])
var _min = Switch( True() ,

"Last 3 Months "eomonth(_max1, -3) +1  ,

"Last Months "eomonth(_max1, -2) +1  ,

"Last 3-6 Months "eomonth(_max1, -6) +1  ,

"Last 6-9 Months "eomonth(_max1, -9) +1 

// add others

)

var _max = Switch( True() ,

"Last 3 Months "eomonth(_max1, 0)   ,

"Last Months "eomonth(_max1,-1)   ,

"Last 3-6 Months "eomonth(_max1, -3)  ,

"Last 6-9 Months "eomonth(_max1, -6) 

// add others

)

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-30/m-p/1284966#M561629

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

[Anonymous]

Hi @amitchandak ,

Thank you for the response, I've tried adding your measure to my date table but I am receiving a calculation error:

'Dates'[FilteredDates]: DAX comparison operations do not support comparing values of type Text with values of type Date.

Bit confused as to what I've done wrong - modified measure as follows:

var _max1 = MAXX(ALLSELECTED('Date'),'Date'[Date Name])
var _min = SWITCH( TRUE() ,

"Last Month" = EOMONTH(_max1, -2) +1 ,
"Last 1-3 Months" = EOMONTH(_max1, -3) +1 ,
"Last 3-6 Months" = EOMONTH(_max1, -6) +1 ,
"Last 6-9 Months" = EOMONTH(_max1, -9) +1 ,
"Last 9-12 Months" = EOMONTH(_max1, -12) +1,
"12+ Months" = EOMONTH(_max1, 12) +1)

var _max = SWITCH( TRUE(),

"Last Month" = EOMONTH(_max1, -1) ,
"Last 1-3 Months" = EOMONTH(_max1, 0) ,
"Last 3-6 Months" = EOMONTH(_max1, -3) ,
"Last 6-9 Months" = EOMONTH(_max1, -6) ,
"Last 9-12 Months" = EOMONTH(_max1, -9) ,
"12+ Months" = EOMONTH(_max1, 12) +1)

RETURN

CALCULATE(
    SUM('Orders'[Order Total Amount]), 
        FILTER('Date', 'Date'[Date Name] >=_min && 'Date'[Date Name] <= _max),
        USERELATIONSHIP('Orders'[InvoiceCreatedAt], 'Date'[Date Name])
        )

[Anonymous]

HI @Anonymous,

I think these should be related to your expressions and you are writing conditions to compare text and date values in the 'switch' function. You can try to use the following measure formula if helps:

formula =
VAR selection =
    SELECTEDVALUE ( Table[Segment] )
VAR _max1 =
    MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date Name] )
VAR _min =
    EOMONTH (
        _max1,
        SWITCH (
            selection,
            "Last Month", -2,
            "Last 1-3 Months", -3,
            "Last 3-6 Months", -6,
            "Last 6-9 Months", -9,
            "Last 9-12 Months", -12,
            "12+ Months", 12
        )
    ) + 1
VAR _max =
    IF (
        selection <> "12+ Months",
        EOMONTH (
            _max1,
            SWITCH (
                selection,
                "Last Month", -1,
                "Last 1-3 Months", 0,
                "Last 3-6 Months", -3,
                "Last 6-9 Months", -6,
                "Last 9-12 Months", -9
            )
        ),
        EOMONTH ( _max1, 12 ) + 1
    )
RETURN
    CALCULATE (
        SUM ( 'Orders'[Order Total Amount] ),
        FILTER ( 'Date', 'Date'[Date Name] >= _min && 'Date'[Date Name] <= _max ),
        USERELATIONSHIP ( 'Orders'[InvoiceCreatedAt], 'Date'[Date Name] )
    )

Regards,

Moonlight

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thank you for your assistance!

Unfortunately I am still receiving the same error which is quite puzzling as none of the data is stored as Text.

I've done a workaround for now; using SQL to create a date table, checking how many Months in the Past a Date is from the current server time, and then applying that as a filter on separate cards for each category.