Forum Discussion
Chart YTD for multiple years from monthly data
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.
Super Userbjw210 , 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
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.