Month segmentation so that when selecting it, all previous months can be observed (aggregate).

[Francisco_MS_PB]

Hello,

I have a page with two segmentations: Year (2021,2022,2023) and Month (1,2,3,4,5,6,7,8,9,10,11,12). I have several visualizations that depends on DAX measures which work correctly if I select any month or group of months (e.g 2023-01 or 2023-01,02,03,04,05,06,07,08).

I wanted to improve the user experience with a segmentation or a button called "YTD", that when I select it, what it does is directly filter the entire page with all the months prior to the selected month. For example if I am visualizing the page with data from 23-08, when selecting this option "YTD", automatically the visualization of the page will be 23-01,02,03,04,05,06,07,08, to see the aggregate. If you had 22-04 checked, what you would see is 22-01,02,03,04. Is like a quick option to avoid selecting each prior month separetly.

Bookmarks is not an option, it is really messy with so many possible selections. 

Thanks, any advice is greatful!

[Anonymous · Accepted solution]

Hi @Francisco_MS_PB ,

I think you need to create a DimDate table with inactive relation with calendar table to help calculation.

My Data model:

I suggest you to try code as below to create a measure.

Measure = 
VAR _SELECTYEAR =
    SELECTEDVALUE ( 'YearMonthSlicer'[Year] )
VAR _SELECTMONTH =
    SELECTEDVALUE ( 'YearMonthSlicer'[MonthSort] )
VAR _SUM1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        USERELATIONSHIP ( 'Calendar'[YearMonth], YearMonthSlicer[YearMonth] )
    )
VAR _SUM2 =
    IF (
        MAX ( 'Calendar'[Year] ) = _SELECTYEAR
            && MAX ( 'Calendar'[MonthSort] ) <= _SELECTMONTH,
        CALCULATE ( SUM ( 'Table'[Value] ) )
    )
RETURN
    IF ( ISFILTERED ( 'Select'[Button] ), _SUM2, _SUM1 )

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What is your expected result when the users select multiple months and/or multiple years ?