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

     

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