Forum Discussion
YOU chart for multiple products
Hello, I am having trouble creating a chart in Power BI that I can easily create in Excel. I want to show, in a line chart, the volume of two products for current year and last year.
My data looks like the below:
| Product | Date | Volume |
| Product A | 01-Jan-19 | 100 |
| Product A | 02-Jan-19 | 200 |
| Product A | 03-Jan-19 | 100 |
| Product A | 04-Jan-19 | 300 |
| Product A | 05-Jan-19 | 100 |
| Product A | 01-Jan-20 | 100 |
| Product A | 02-Jan-20 | 100 |
| Product A | 03-Jan-20 | 100 |
| Product A | 04-Jan-20 | 100 |
| Product A | 05-Jan-20 | 100 |
| Product B | 01-Jan-19 | 400 |
| Product B | 02-Jan-19 | 400 |
| Product B | 03-Jan-19 | 400 |
| Product B | 04-Jan-19 | 400 |
| Product B | 05-Jan-19 | 400 |
| Product B | 01-Jan-20 | 300 |
| Product B | 02-Jan-20 | 300 |
| Product B | 03-Jan-20 | 300 |
| Product B | 04-Jan-20 | 300 |
| Product B | 05-Jan-20 | 300 |
Does anyone know how to make it work?
4 Replies
Add a calendar table. It should have three columns.
- Date
- Month/Day
- Year
Link that calendar table to your transaction date.
Use the Month/Day column as your X axis, the Year columns as the legend, and the Volume fact column in the value area.
Anonymous , with a date table and time intelligence
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) //Only year vs Year, not a level below This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense) This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]))) Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38aTo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Appreciate your Kudos.
