Forum Discussion
DAX for deltas
Hi,
I am trying to show trend line with data for about a two years. i have created year slicers for the last three years. So when I click the slicer for the current year the data seems to right and the trend line is showing what I need( difference between this weeks data and the previous week with delta sign). When I click the previous year and current year on the slicer , I am able to see two trend lines , but when I click on the data points, the delta sign in the KPI card is showing reverse . For example, when I select data for 2019 and 2020 and August 3rd week, it is performing the difference between current week and previous week for 2019 and difference between current and previous week of 2020 , and perfomring the sum of both the results and showing on the delta KPI. What i need is the diffrence between weeks for individual year based on which data point is selected. ( 2020 or 2019 etc).
Below are the 6 measure i'm using . Top 4 for delta in the card and last 2 for line graph.
4 Replies
- amitchandak
Super User
vkasula01 , See if my week on week blog can help
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123example
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))The measure which I create is working fine. when I select single year delta(up or down arrow) are working fine.
But when I select trend line for multipul years if the user clicks on any of the data point in one of the trend line it sums up the value for that particular week for all the year give the delta based on the sum we get for particular week.
Measure used for creating line graph:
surcharge2019 = CALCULATE(SUM(Summary[surcharge]),FILTER(Summary,YEAR(Summary[Date]) = 2019))surcharge2020 = CALCULATE(SUM(Summary[Rsurcharge]),FILTER(Summary,YEAR(Summary[Date]) = 2020))Measure used for creating delta(Up or down arrow):1) 2_Down = IF(HASONEVALUE(Summary[WeekNum]) && Sum(Summary[surcharge])> SUM(Summary[Column surcharge]),UNICHAR(9650),"")2) 2_UP = IF(HASONEVALUE(Summary[WeekNum]) && Sum(Summary[ surcharge])< SUM(Summary[Column surcharge]),UNICHAR(9660),"")3) Opt_RatioG = var A=CALCULATE(SUM(Summary[surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum]))*100 VAR B = CALCULATE(SUM(Summary[Column surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum])) *100 Return(IF(HASONEVALUE(Summary[WeekNum]) = FALSE() && A< B, UNICHAR(9660),""))4) Opt_RatioR = var A=CALCULATE(SUM(Summary[surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum]))*100VAR B = CALCULATE(SUM(Summary[Column surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum])) *100 Return(IF(HASONEVALUE(Summary[WeekNum]) = FALSE() && A> B, UNICHAR(9650),""))The requiment is when a user clicks on one of the data point the delts should be crearted based on the particular weeky number for the seleted line(seleted year).
What I can think is to create a slicer table for 2019 and 2020, then filter the trend measures to be displayed in the chart. Please see the attatched sample pbix, I used random values just to show the idea.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.The measure which i create is working fine. when i select single year delta(up or down arrow) are working fine.
But when I select trend line for multipul years if the user clicks on any of the data point in one of the trend line it sums up the value for that particular week for all the year give the delta based on the sum we get for particular week.
Measure used for creating line graph:
surcharge2019 = CALCULATE(SUM(Summary[surcharge]),FILTER(Summary,YEAR(Summary[Date]) = 2019))surcharge2020 = CALCULATE(SUM(Summary[Rsurcharge]),FILTER(Summary,YEAR(Summary[Date]) = 2020))Measure used for creating delta(Up or down arrow):1) 2_Down = IF(HASONEVALUE(Summary[WeekNum]) && Sum(Summary[surcharge])> SUM(Summary[Column surcharge]),UNICHAR(9650),"")2) 2_UP = IF(HASONEVALUE(Summary[WeekNum]) && Sum(Summary[ surcharge])< SUM(Summary[Column surcharge]),UNICHAR(9660),"")3) Opt_RatioG = var A=CALCULATE(SUM(Summary[surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum]))*100 VAR B = CALCULATE(SUM(Summary[Column surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum])) *100 Return(IF(HASONEVALUE(Summary[WeekNum]) = FALSE() && A< B, UNICHAR(9660),""))4) Opt_RatioR = var A=CALCULATE(SUM(Summary[surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum]))*100VAR B = CALCULATE(SUM(Summary[Column surcharge]), FILTER(Summary,MAX(Summary[WeekNum]) = Summary[WeekNum])) *100 Return(IF(HASONEVALUE(Summary[WeekNum]) = FALSE() && A> B, UNICHAR(9650),""))The requiment is when a user clicks on one of the data point the delts should be crearted based on the particular weeky number for the seleted line(seleted year).
