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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Community Support rankCommunity 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.