Forum Discussion

vkasula01's avatar
vkasula01
New Member
6 years ago

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 a...
Need Help
Show and Tell
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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/1051123

 

example

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)))

vkasula01's avatar
vkasula01
New Member
6 years ago

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]))*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(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).