Forum Discussion

Bjar2023's avatar
Bjar2023
New Member
3 years ago
Solved

How to Categorize Dates based on Relative Date Slicer

I have a report with a relative date slicer.  Example: 

 

I have created formulas for the min and max of those dates.  Example:

Min Display Date = CALCULATE ( Min( 'Date Table'[Date] ), ALLSELECTED ( 'Date Table'[Date] ) )
Max Display Date = CALCULATE ( Max( 'Date Table'[Date]), ALLSELECTED ( 'Date Table'[Date] ) )
 
I need a way to pass those min and max display dates onto the data set so I can categorize the dates (instead of filter).  I have tried creating a duplicate date table that creates the range and then apply it to the other date table, but I get a flurry of errors.  I need a formula that essentially does this:   

Any help would be extemely useful.  Thanks.  

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Bjar2023 ,

    Please have a try.

    Create a table.

    Period = {"Same Period last year","Selected Period"}

    Then create a measure.

    PeriodValue =
VAR _a =
    ALLSELECTED ( 'Date'[Date] )
VAR _b =
    SELECTCOLUMNS ( _a, "Last Year", EDATE ( [Date], -12 ) )
RETURN
    SUMX (
        'Period',
        SWITCH (
            [Period],
            "Selected Period", SUMX ( FILTER ( 'Table', [date] IN _a ), [value] ),
            "Same Period last year", SUMX ( FILTER ( 'Table', [date] IN _b ), [value] )
        )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Bjar2023 ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a measure.

    Measure = var _maxdae=EDATE([Max Display Date],-12)
var _mindate=EDATE([Min Display Date],-12)
return
IF(MAX('Table'[Date])<=[Max Display Date]&&MAX('Table'[Date])>=[Min Display Date],"Selected Period",IF(MAX('Table'[Date])<=_maxdae&&MAX('Table'[Date])>_mindate,"Same Period last year"))

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Bjar2023's avatar
    Bjar2023
    New Member
    3 years ago

    So this worked in categorizing the dates, but since its a measure, I cant create any sort of groupings from it.  Ultimately, I want to sum metrics using this category.  Example:

    Total Revenue

    Selected Period: $5,000

    Same Period Last Year: $7,000

    I may be oversimplifying, but is there an easy way to convert the measure to a column so I can create visuals from this grouping? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Bjar2023 ,

    Please have a try.

    Create a table.

    Period = {"Same Period last year","Selected Period"}

    Then create a measure.

    PeriodValue =
VAR _a =
    ALLSELECTED ( 'Date'[Date] )
VAR _b =
    SELECTCOLUMNS ( _a, "Last Year", EDATE ( [Date], -12 ) )
RETURN
    SUMX (
        'Period',
        SWITCH (
            [Period],
            "Selected Period", SUMX ( FILTER ( 'Table', [date] IN _a ), [value] ),
            "Same Period last year", SUMX ( FILTER ( 'Table', [date] IN _b ), [value] )
        )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.