Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering relative date ranges

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 r...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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])
        )