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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        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.