YTD with year month slicer combined with 12 months back functionality

[Anonymous]

Hi guys,

I need your help again 🙂

I've implemented one of Alberto's awesome calculation items to get various measures to show the 12 months back values in charts. https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

To have this data I had to change my year + month slicer to the combo YYYYMM slicer.

All KPI's and charts are working fine except the YTD KPI card.
The KPI YTD card is currently showing the sum of all years in the source, and I can't get it to show the sumof months for the selected year in the year/month (YYYYMM) slicer.

I tried a bunch of DAX tweaking, to remove one filter context and push a different one but nothing is working. Can anyone help with this? I've referred to one of the member's code from the community but didn't work in my report.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/YTD-with-SelectedValue-of-Year-Month-Slicer/m...

Value ROS YTD =

// Var ROSYTD =

// TOTALYTD(

// ValueROS,

// dim_date[CalendarDate])

//Var ROSYTD_filter =

//Calculate(

//ROSYTD,

//FILTER(

//ALL(factsaleout),

//left(factsaleout[dimdatesk],6) >= LEFT(SELECTEDVALUE(dim_date[YearMonth]),4)&"01" &&

// left(factsaleout[dimdatesk],6) <= SELECTEDVALUE(dim_date[YearMonth])))

Var ROSYTD1 =

CALCULATE(

ValueROS,

DATESYTD(dim_date[CalendarDate]))

Return

ROSYTD1

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I create a sample.

In my understanding, when you select a value in the Year-Month slicer, you want to get the sum sales of date in the selected year, here's my solution.

Create a measure.

KPI YTD =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
    )
)

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi v-kalyj-msft,

Not quite what I need 🙂 since this shows the YTD for the last month 12-2021 or 06-2022. I need when I select 03-2022 to show 03-2022 YTD, now it shows 06-2022 YTD when 03-2022 is selected.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

I misunderstood it. I modify the formula like this:

KPI YTD =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    DATESYTD ( 'Table'[Date] ),
    REMOVEFILTERS ( 'Table'[Year-Month] )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Yes this is it! 🙂 Thank you so much, I made a measure that worked but was way to long, this one works like a charm 🙂