Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic date range based on selected date in slicer

Hi everyone,

 

I want to create a graph that on X-axis has a date range of 13 Months or 13 Weeks based on the Selected End Date of a date range slicer (see picture 2 below).

13 Months or 13 Weeks would be a Slicer that should store 'Last 13 Months' and 'Last 13 Weeks' columns I want to create.

 

Here's how it should look.

 

I wanted to take the approach of creating a measure:

SelectedEndDate = MAX(DimCalendar[Date])

 

And based on that:

Last 13 Months = 

IF(
    [Date] >= EDATE([SelectedEndDate], -13), 
    COMBINEVALUES(
        "- ",
        YEAR([Date]),
        FORMAT([Date], "MM") 
    ),
    BLANK()
)
 
and
 
Last 13 Weeks = 
IF(
    [Date] >= [SelectedEndDate] - 7 * 13,
    COMBINEVALUES(
        "- WK",
        YEAR([Date]),
        FORMAT(WEEKNUM([Date]), "00")
    ),
    BLANK()
)
 
But the problem is the date range slicer user has to chose.

 

Whatever I do it will never go past the Start Date in the past on my graph, and If I remove the interaction between the graph and the date range slicer, the calculations consider End Date to be the last day in the data source.

 

Creating custom measures through the values is difficult because as you can see on the first picture, the user has to select them as well and chose between the 2 to compare, and there is a lot of them.

 

Is there a way to do this?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    I found the solution.

    1. I first created copy of my Calendar table and created the the relationship to the Calendar table but did not keep it active.

     

    2. Than in Tabular Editor I created the Calculated group that I use in filter of the visual:

    VAR NumOfMonths = MAX(PeriodsTable[Value]) -- This will always use the value from the PeriodTable
    VAR ReferanceDate = MAX ( 'DimCalendar'[Date] )
    VAR PreviousDates =
             DATESINPERIOD ( 'PrevCalendar'[Date],
                      ReferanceDate,
                      -NumOfMonths,
                      MONTH )
    VAR Result =
             CALCULATE ( SELECTEDMEASURE(),
                      REMOVEFILTERS( 'DimCalendar' ),
                      KEEPFILTERS ( PreviousDates ),
                      USERELATIONSHIP('DimCalendar'[Date],
                      'PrevCalendar'[Date]) )
    RETURN Result

     

    3. Then I created the Date Period Parameter from copy of the Calendar table that has Monthly and Weekly format I need:

    Date Period Parameter = {
        ("Month ", NAMEOF(PrevCalendar[Year Month Name]), 0),
        ("Week", NAMEOF(PrevCalendar[Year Week Number]), 1)
    }
     
    4. Also I created the connection PeriodsTable:
    PeriodsTable =
    DATATABLE(
        "Period", STRING,
        "Value", INTEGER,
        "Order",INTEGER,
        {
            {"Month", 12,0},
            {"Week", 3,1}
        }
    )
     
    5. Then I created relationship between Date Period Parameter and PeriodsTable on 'Date Period Parameter'[Date Period Order] and 'PeriodsTable[Order] in One to One relationship.
     
    6. On the graph, I used 'Date Period Parameter'[Date Period] on the X-axis (which is also in slicer) and then added the filter from the Calculated group to the Filter pane of the visual.
     
    The way it works, the Calculated group is calculating the date range based on Selected End Date and the PeriodsTable values are deciding if it's 12 months before or 3 months before, while the Date Period slicer is presenting the data in the graph based on the format it is chosen.

     

2 Replies

  • NordicDrow's avatar
    NordicDrow
    Helper I
    1 year ago

    Hi there!

    I think you should be able to, given date column, generate a "Year-Month" and "Year-Week" column, then unpivot them to rows (attribute-value).

    From there create a slicer to select between "Year-Month/Year-Week" (attribute column).
    And use a TOPN 13 values in filters panel.

    Otherwise, this is a similar topic:
    https://community.fabric.microsoft.com/t5/Desktop/Select-Last-N-Months-from-selected-month-N-Weeks-from-selected/m-p/4244835#M1333933

    Hope this helps, if you need a better example or any other help please tell me. Also, a data sample would come in handy.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I found the solution.

    1. I first created copy of my Calendar table and created the the relationship to the Calendar table but did not keep it active.

     

    2. Than in Tabular Editor I created the Calculated group that I use in filter of the visual:

    VAR NumOfMonths = MAX(PeriodsTable[Value]) -- This will always use the value from the PeriodTable
    VAR ReferanceDate = MAX ( 'DimCalendar'[Date] )
    VAR PreviousDates =
             DATESINPERIOD ( 'PrevCalendar'[Date],
                      ReferanceDate,
                      -NumOfMonths,
                      MONTH )
    VAR Result =
             CALCULATE ( SELECTEDMEASURE(),
                      REMOVEFILTERS( 'DimCalendar' ),
                      KEEPFILTERS ( PreviousDates ),
                      USERELATIONSHIP('DimCalendar'[Date],
                      'PrevCalendar'[Date]) )
    RETURN Result

     

    3. Then I created the Date Period Parameter from copy of the Calendar table that has Monthly and Weekly format I need:

    Date Period Parameter = {
        ("Month ", NAMEOF(PrevCalendar[Year Month Name]), 0),
        ("Week", NAMEOF(PrevCalendar[Year Week Number]), 1)
    }
     
    4. Also I created the connection PeriodsTable:
    PeriodsTable =
    DATATABLE(
        "Period", STRING,
        "Value", INTEGER,
        "Order",INTEGER,
        {
            {"Month", 12,0},
            {"Week", 3,1}
        }
    )
     
    5. Then I created relationship between Date Period Parameter and PeriodsTable on 'Date Period Parameter'[Date Period Order] and 'PeriodsTable[Order] in One to One relationship.
     
    6. On the graph, I used 'Date Period Parameter'[Date Period] on the X-axis (which is also in slicer) and then added the filter from the Calculated group to the Filter pane of the visual.
     
    The way it works, the Calculated group is calculating the date range based on Selected End Date and the PeriodsTable values are deciding if it's 12 months before or 3 months before, while the Date Period slicer is presenting the data in the graph based on the format it is chosen.