dynamic data range

[Anonymous]

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:

DateKey	UnitsSold
1	1
2	5
3	2
4	10
5	3
6	4
7	2
8	7
9	7
10	4
11	3
12	6
13	11
14	6
15	4
16	2
17	9
18	4
19	4
20	5

dimension date table:

DateKey	Period	PeriodLabel	PeriodPlus3	PeriodPlus3Label
1	1	Per1	3	Per3
2	1	Per1	3	Per3
3	2	Per2	4	Per4
4	2	Per2	4	Per4
5	3	Per3	5	Per5
6	3	Per3	5	Per5
7	4	Per4	6	Per6
8	4	Per4	6	Per6
9	5	Per5	7	Per7
10	5	Per5	7	Per7
11	6	Per6	8	Per8
12	6	Per6	8	Per8
13	7	Per7	9	Per9
14	7	Per7	9	Per9
15	8	Per8	10	Per10
16	8	Per8	10	Per10
17	9	Per9
18	9	Per9
19	10	Per10
20	10	Per10

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?

[amitchandak]

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]

@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 · Accepted solution]

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.

[Greg_Deckler]

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]

@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.