Forum Discussion

HarryBailey's avatar
HarryBailey
Helper II
6 years ago
Solved

Dynamic Date Measures

Hi all,

 

I was looking to see if I can build dynamic date measures that I could use in a slicer. I know how to use dates filters such as YTD within specitic measures themseves, but I want date measures that are overriding for all other measures.

 

The best way to describe this would be quite simply, I have a very simple slicer here based off a seperate financial calendar. When I click week 9, everything in my data returns from WK9, as the dates in my data are linked to the finalcial calendar.

 

 

But what I need is the option for, say YTD, or last 4 weeks as options on the slicer above. So I can just click YTD there, without having to do seperate measures for each field.

 

Financial calendar is below FYI.

 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please take following steps:

    1)Create a new slicer table:

    SlicerTable = 
UNION (
    DISTINCT (
        SELECTCOLUMNS ( 'Financial calendar', "_WeekNo", 'Financial calendar'[WeekNo] )
    ),
    DATATABLE ( "YTD", STRING, { { "YTD" } } )
)

    2)Try this measure:

    Measure = 
IF (
    SELECTEDVALUE ( SlicerTable[WeekNo] ) = "YTD",
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] IN FILTERS ( 'Table'[Year] ) )
    ),
    IF (
        VALUE ( SELECTEDVALUE ( SlicerTable[WeekNo] ) ) = MAX ( 'Table'[WeekNo] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Financial calendar',
                'Financial calendar'[WeekNo] = VALUE ( SELECTEDVALUE ( SlicerTable[WeekNo] ) )
            )
        )
    )
)

    3)When select "YTD" in slicer, the result shows each year's YTD values:

    See my attached pbix file.

     

    Best Regards,

    Giotto

     

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please take following steps:

    1)Create a new slicer table:

    SlicerTable = 
UNION (
    DISTINCT (
        SELECTCOLUMNS ( 'Financial calendar', "_WeekNo", 'Financial calendar'[WeekNo] )
    ),
    DATATABLE ( "YTD", STRING, { { "YTD" } } )
)

    2)Try this measure:

    Measure = 
IF (
    SELECTEDVALUE ( SlicerTable[WeekNo] ) = "YTD",
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] IN FILTERS ( 'Table'[Year] ) )
    ),
    IF (
        VALUE ( SELECTEDVALUE ( SlicerTable[WeekNo] ) ) = MAX ( 'Table'[WeekNo] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Financial calendar',
                'Financial calendar'[WeekNo] = VALUE ( SELECTEDVALUE ( SlicerTable[WeekNo] ) )
            )
        )
    )
)

    3)When select "YTD" in slicer, the result shows each year's YTD values:

    See my attached pbix file.

     

    Best Regards,

    Giotto

     

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi HarryBailey 

     

    To achieve this you will need to build the calculation Measures for each time frame that you need ( YTD, last 4 weeks ) and later use the below technique to switch between them.

    https://www.youtube.com/watch?v=gYbGNeYD4OY

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn