topic Re: YTD with year month slicer combined with 12 months back functionality in DAX Commands and Tips

YTD with year month slicer combined with 12 months back functionality

Anonymous — Thu, 23 Jun 2022 10:20:18 GMT

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-p/1597994

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

Re: YTD with year month slicer combined with 12 months back functionality

v-yanjiang-msft — Mon, 27 Jun 2022 09:29:19 GMT

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.

Re: YTD with year month slicer combined with 12 months back functionality

Anonymous — Wed, 29 Jun 2022 08:01:24 GMT

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.

Re: YTD with year month slicer combined with 12 months back functionality

v-yanjiang-msft — Tue, 05 Jul 2022 02:52:27 GMT

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.

Re: YTD with year month slicer combined with 12 months back functionality

Anonymous — Fri, 15 Jul 2022 13:13:54 GMT

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 🙂