YTD from all years based on selected year and week

[Anonymous]

Hi, 

I need a line graph that show YTD from all years/periods before the selected date. X axis are months.

I have a year slicer and a week slicer. So if i choose for example year 2020 and week 10 then I want to see 2020 line til week 10 and line for all months 2019 and 2018 ( let say I have only data from 2018).

[v-shex-msft · Accepted solution]

HI @Anonymous,

If you want to use slicer to achieve the selector effect instead of filter effect, please create an unconnected table as source of your slicers.

Then you can write measure formula to extract selected value and use them as parameter conditions to filter and calculate on raw table records with cumulative mode.

Measure =
VAR selected =
    MAX ( Selector[Date] )
VAR currDate =
    MAX ( table[Date] )
RETURN
    IF (
        currDate <= selected,
        CALCULATE (
            SUM ( table[amount] ),
            FILTER (
                ALLSELECTED ( table ),
                YEAR ( [date] ) = YEAR ( currDate )
                    && [date] <= currDate
            )
        )
    )

BTW, I'm not so sure the table structure, can you please provide some dummy data to help us clarify their structure and do test to coding formula?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng