Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

dynamic data range

I need a visual to return a range of periods (a month-like time window) according to a selection in a slicer.  The range will always be 3 periods, and it will begin with the period selected in the slicer.  So, for example, if period "Per3" is selected, then the visual needs to display only periods Per3, Per4, and Per5; similarly, if period "Per6" is selected, then the visual needs to display only periods Per6, Per7, and Per8.  How can I achieve this?  I have tried to follow the solution method below, but it's not working for me.  I suspect that it has something to do with the fact that I'm using a dimension model (not a completely denormalized model) and the fact that I have already calculated the ending period to use in the time range.

 https://radacad.com/dynamic-date-range-from-slicer

 

Below is my data:

 

fact table:

DateKeyUnitsSold
11
25
32
410
53
64
72
87
97
104
113
126
1311
146
154
162
179
184
194
205

 

dimension date table:

DateKeyPeriodPeriodLabelPeriodPlus3PeriodPlus3Label
11Per13Per3
21Per13Per3
32Per24Per4
42Per24Per4
53Per35Per5
63Per35Per5
74Per46Per6
84Per46Per6
95Per57Per7
105Per57Per7
116Per68Per8
126Per68Per8
137Per79Per9
147Per79Per9
158Per810Per10
168Per810Per10
179Per9  
189Per9  
1910Per10  
2010Per10  

 

Here is my data model:

 

As you can see, I copied the DimensionDate table as the DimensionDateSlicer table and left it disconnected from the main data model (which is necessary for the solution method).  (I also like to put all of my measures per model on a separate empty table, which is the DummyForMeasures table.)

 

To make sure that the sorting is correct, below is my sorting (on both the DimensionDate and DimensionDateSlicer tables):

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here are my measure definitions:

 

Dummy measure for filter = 
var DimensionDatePeriod = MIN('DimensionDate'[Period])
var SlicerSelection = MIN('DimensionDateSlicer'[Period])
var DimensionDatePeriodPlus3 = MIN('DimensionDate'[PeriodPlus3])

RETURN
    IF(
        (DimensionDatePeriod >= SlicerSelection)
        && (DimensionDatePeriod <= DimensionDatePeriodPlus3)
        ,1 --arbitrary indicator for TRUE
    )

 

 

Total Units Sold = SUM('Fact'[UnitsSold])

 

 

Here is the visual configuration for my slicer.  (As you can see, PeriodLabel here comes from DimensionDateSlicer, not DimensionDate.)

 

Here is the configuration for my clustered column chart.  (As you can see, PeriodLabel here comes from DimensionDate, not DimensionDateSlicer, which is necessary for the solution method.  And, [Dummy measure for filter] is set to 1 on the filter pane for this visual.)

 

As you can see with 'Per3' selected, I am incorrectly displaying 6 periods.  It should display only 3 periods.  What am I doing wrong, and how do I resolve?

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Dummy measure for filter =
VAR DimensionDatePeriod =
    MIN ( 'DimensionDate'[Period] )
VAR SlicerSelection =
    MIN ( 'DimensionDateSlicer'[Period] )
VAR DimensionDatePeriodPlus3 =
    MIN ( 'DimensionDateSlicer'[PeriodPlus3] ) ------------Changed.
RETURN
    IF (
        ISBLANK ( SELECTEDVALUE ( DimensionDateSlicer[Period] ) ), ----------Added.
        1,
        IF (
            ( DimensionDatePeriod >= SlicerSelection )
                && ( DimensionDatePeriod <= DimensionDatePeriodPlus3 ),
            1
        )
    )

     

     

     

    Best Regards,

    Icey

     

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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Wait, why does Per6 display 6,7 and 10 or is that a typo?

     

    If your slicer is connected to your main table? You may need a disconnected table for you slicer.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

      You're correct.  It's a typo.  I just corrected my post.  Sorry about that.

       

      As you can see in my data model picture, the field for my slicer comes from a copy of my Dimension Date table, which is disconnected from my data model.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I did not get you completely. But something like this should work for you

     

    Measure =
var _minPrd = maxx(filter('dimension date','dimension date'[Period label]
					=(maxx('dimension date',allselected('dimension date'[Period label])))),'dimension date'[Period])


var _maxPrd = _minPrd+3

return
calculate(sum(Fact[Unit Sold]),filter(all('dimension date'),'dimension date'[Period]>=_minPrd && 'dimension date'[Period]<=_maxPrd))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Proud to be a Datanaut Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      The data model that I posted is a simplified version of my actual data model.  In my actual data model, I can't simply add 3 to Period to obtain PeriodPlus3.  In my actual data model, my Period values are like 20062007.01 (to represent the first period of fiscal year 2006-2007) and 20192020.12 (to represent the last period of fiscal year 2019-2020).  That is why my Dimension Date table includes columns for PeriodPlus3 / PeriodPlus3Label.  In other words, I've already done the work (in the creation of the Dimension Date table ) to create the PeriodPlus3 value for every Period value.  (It doesn't need to -- and shouldn't -- be done in DAX.)  Is there a way to set _maxPrd from the PeriodPlus3 value already in the table?  (I think this is one of the things that makes my data model unique, as every solution I've seen for this problem always computes the _maxPrd variable through math, rather than fetching it from a table.)

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Try this:

        Dummy measure for filter =
VAR DimensionDatePeriod =
    MIN ( 'DimensionDate'[Period] )
VAR SlicerSelection =
    MIN ( 'DimensionDateSlicer'[Period] )
VAR DimensionDatePeriodPlus3 =
    MIN ( 'DimensionDateSlicer'[PeriodPlus3] ) ------------Changed.
RETURN
    IF (
        ISBLANK ( SELECTEDVALUE ( DimensionDateSlicer[Period] ) ), ----------Added.
        1,
        IF (
            ( DimensionDatePeriod >= SlicerSelection )
                && ( DimensionDatePeriod <= DimensionDatePeriodPlus3 ),
            1
        )
    )

         

         

         

        Best Regards,

        Icey

         

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