Dynamic Date Measures

[HarryBailey]

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.

 

[v-gizhi-msft · Accepted solution]

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]

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

[amitchandak]

@HarryBailey , Not very clear, Are you looking for - https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...