Actual and budget ytd for each year based on a month and a year slicer in the same chart

[Anonymous]

Hi all,

I need your help to achieve the following need:

- I have a month slicer and a year slicer

- Then based on what is selected : I calculate the actual ytd for that year and all each previour year for the same month, and the budget ytd only for the selected year for that month

- The X-axis is years (the year selected in my slicer will be the max of my X-axis chart)

For example:

///

If I select the month 05 and the year 2019

Then : my chart will show the actual ytd and the budget ytd for 2019 (from 01 to 05 )

and the actual ytd for 2018 (from 01 to 05 )

and the actual ytd for 2017 (from 01 to 05 )

..... and all previous years in my data table

///

If I select the month 02 and the year 2017

Then : my chart will show the actual ytd and the budget ytd for 2017 (from 01 to 02 )

and the ytd sales for 2016 (from 01 to 02 )

and the ytd sales for 2015 (from 01 to 02 )

.....and all previous years in my data table

I am so confused and don't know even if that is possible...

Many thanks for your help, much appreciated.

Regards,

[v-xicai]

Hi @Anonymous ,

You may create calendar table first of all, create relationship between the data table and calendar table on date field.

Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))  

Or

Calendar =CALENDARAUTO() .

Then you can create measures like DAX below.

YTD for CY = TOTALYTD(SUM(Data[Sales]),Calendar[Date])

YTD for PY = CALCULATE(SUM(Data[Sales]),SAMEPERIODLASTYEAR(Calendar[Date]))

Best Regards,

Amy

Community Support Team _ Amy

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

[Anonymous]

Hello,

many thanks for your replies.

But this way, I have to create one measure for each year (this year, previous year....).

My need is to create one measure that manage all years (I need that beacuse I need to use a Legent in my chart => using the legend implicates only one measure as a value).

Any input?

Regards,

[amitchandak]

First Make sure you have date dimension and it marked as a date table in model view. From this table use month and year filter.

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Try these

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date Filer])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-1,Year)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601