Chart YTD for multiple years from monthly data

[bjw210]

I have a data set with monthly sales data that I'm trying to chart in a YTD view. If I have only one "scenario" a year I can use the year in the legend and it would work perfectly. However, I have multiple scenarios (Actuals, budget, forecast, etc) that can be in a given year. In the image below, the top left is MTD, the top right is YTD and you can see that the 2021 Actuals series no longer shows up on this chart (presumably because I'm using the TotalYTD function and 2022 is the latest year in the data). The formula works and calculates the right amounts based on the numerical table below the charts as long as it can pass the year, then the year scenario to the calculation. Any ideas on which measure would make all scenarios from each year show up in the YTD chart?

Sample data used for the charts/table:
Month                                           Scenario         Amount

Saturday, January 1, 2022	2022 Budget	100
Tuesday, February 1, 2022	2022 Budget	100
Tuesday, March 1, 2022	2022 Budget	99
Friday, April 1, 2022	2022 Budget	97
Sunday, May 1, 2022	2022 Budget	97
Wednesday, June 1, 2022	2022 Budget	94
Friday, July 1, 2022	2022 Budget	91
Monday, August 1, 2022	2022 Budget	89
Thursday, September 1, 2022	2022 Budget	89
Saturday, October 1, 2022	2022 Budget	89
Tuesday, November 1, 2022	2022 Budget	89
Thursday, December 1, 2022	2022 Budget	89
Saturday, January 1, 2022	2022 Actuals	100
Tuesday, February 1, 2022	2022 Actuals	103
Tuesday, March 1, 2022	2022 Actuals	105
Friday, April 1, 2022	2022 Actuals	103
Sunday, May 1, 2022	2022 Actuals	103
Wednesday, June 1, 2022	2022 Actuals	104
Friday, July 1, 2022	2022 Actuals	102
Monday, August 1, 2022	2022 Actuals	101
Thursday, September 1, 2022	2022 Actuals	100
Saturday, October 1, 2022	2022 Actuals	101
Tuesday, November 1, 2022	2022 Actuals	102
Thursday, December 1, 2022	2022 Actuals	103
Friday, January 1, 2021	2021 Actuals	100
Monday, February 1, 2021	2021 Actuals	100
Monday, March 1, 2021	2021 Actuals	98
Thursday, April 1, 2021	2021 Actuals	97
Saturday, May 1, 2021	2021 Actuals	94
Tuesday, June 1, 2021	2021 Actuals	91
Thursday, July 1, 2021	2021 Actuals	89
Sunday, August 1, 2021	2021 Actuals	91
Wednesday, September 1, 2021	2021 Actuals	88
Friday, October 1, 2021	2021 Actuals	89
Monday, November 1, 2021	2021 Actuals	87
Wednesday, December 1, 2021	2021 Actuals	89

[Ashish_Mathur]

Hi,

Based on that data that you have shared, please show the expected result in a Table format.

[bjw210]

Below is the expected Chart which would have all 3 scenarios on it. 2022 Actuals, 2022 Budget, and 2021 Actuals. I can only seem to get one year of scenarios to show up on the chart at one time.

[Ashish_Mathur · Accepted solution]

Hi,

It looks like the limitation in a line chart is that one cannot drag more than 1 measure to Y-axis.  Therefore, i have used the Line and Clustered column chart.  Can you live this with?

You may download the PBI file from here.

Hope this helps.

[bjw210]

Hi Ashish, this looks nice but unfortunately won't work for my situation. 

Thanks

[amitchandak]

@bjw210 , As you month is in date format, or create a date from month, Join it with date table and then use dateytd

example

YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('Date'[Date],"12/31"))

you can also add a filter

YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('Date'[Date],"12/31"), filter(Table, Table[Scenario] = "Budget") )

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[bjw210]

Sorry, had projects come up and just coming back to this one. I ended up doing something similar to what you're talking about but running into a seperate problem. 

Using the DatesYTD seems to only work for the most recent year if multiple years exist where i need to pass filters. When I use the filter funtion, I can pass the right year to the measure but it won't calculate for periods that don't have transactions. 

In the below,

• I can get DatesYTD to work for both actuals and budget for the most recent year in the table, but doesn't work for prior years (end table will have data back 5+ years so I wouldnt' be able to use SAMEPERIODLASTYEAR). DatesYTD seems to calculate across all months even when no transactions exist
• I can get Filters to work for all periods and scenarios however, it does not calculate for periods that did not have a transaction. This means a month with no transaction would show 0 for YTD which would be wrong. The Filter Column in the below should show amounts in each month regardless of having a transaction.

DatesYTD =

    CALCULATE(

        SUM(Sheet2[Amount])

        ,DATESYTD(Dates[Date])

    )

Filter =

    Var __Year = LEFT(SELECTEDVALUE(Sheet2[Scenario]),4)

    Var __Month = LOOKUPVALUE(Dates[Month Number],Dates[Month Name],SELECTEDVALUE(Dates[Month Name]))

    Var __Date = DATE(__Year,__Month,1)    

    Var __Result =

        CALCULATE(

            SUM(Sheet2[Amount])

            ,FILTER(ALL(Dates),Dates[Year]=__Year && Dates[Date]<=__Date)

        )

Return __Result