Forum Discussion
Helper IIMultiple relationship on Calendar Date , Fact Table, Dimension Table
Hi,
I am new on building reports on PowerBI, I had experience on Qlik, I am struggling with something that might be very simple for any power bi expert.
When I build a PIE chart on YTD Sales and channel_Category_3, and CLIC on the Blue Category .
I produce a problem on YTD last year KPI as produce BLANK values.
I need that if I CLIC on the Pie chart and CLIC on any value from Channel_category_3 also last YTD KPI filter accordingly this interaction, I guess that because they are both selected from the same table is like I am filtering only this year simultaneously. If I do the filter from a visual filter of channel_category_3 it works.
this_year_sales =
CALCULATE([sales_variable],
FILTER('sales', year('sales'[Fecha]) = YEAR(TODAY())))
last_year_sales =
CALCULATE([sales_variable],
FILTER('sales', year('sales'[Fecha]) = YEAR(TODAY())-1))
_______________________________________________
MODEL
I have a data model with 4 Tables (just to simplify):
- Calendar Table, where all tables link through date field (Fecha in spanish).
- Sales table, with categorical dimension channel_category_3 (linked in blue) and linked with calendar table by datefield (fecha).
- Target sales tables linked with calendar table by datefield, and also contains channel_category_3
To being able to make a relationship on channel_category_3 I have also build a channel_category table.
- Channel_category_3
2 Replies
- amitchandak
Super User
luisccmm , With help from time intelligence and date table, try measures like given example
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 belowThis 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))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
