Dynamic % Difference Dax when filtering on non consecutive years
Hi there, I have a matrix in power BI, items in rows, years in columns and a YOY% difference measure. I can;t show actual data, so here is an example in excel of what I'm doing: Problem is I'm not always comparing to previous year. Sometimes we may want to compare 2019 Vs 2022. This works in excel: Excel calculates the difference in the filtered period. However when I do this in Power BI, when I filter it returns the previous year % difference not filtered period. In power BI I am using a matrix like this: Fruit YoY% (from quick measures) code is: Fruit YoY% = IF( ISFILTERED('Date Table'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE( SUM('Fruit'[Value]), DATEADD('Date Table'[Date].[Date], -1, YEAR) ) RETURN DIVIDE(SUM(Fruit[Value]) - __PREV_YEAR, __PREV_YEAR) ) Which works for YoY, but when I filter, eg, 2020 Vs 2022, it still shows 2022 Vs 2021 % dif. When I use the below code, it works when two filters are on, but defaults to 2019 Vs 2022: Fruit Earlier = VAR earlieryear = calculate( min ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = earlieryear) Fruit Later = VAR lateryear = calculate( max ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = lateryear) fruit % dif = var lateryear = calculate (max('Date Table'[Year]), allselected ('Date Table')) var maxyear = max ('Date Table'[Year]) var earlier_ = [fruit Earlier] return if (lateryear = maxyear, divide ([fruit later]-earlier_ , earlier_)) Ideally, I'd need top level to be YoY%, (across all years if possible but 2022 Vs 2021 would be fine), then the slice the years to get the below: So ideally it would be: 1. YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc) 2. Slicer two years (eg 2022 Vs 2020) 3. Filtered years % difference eg 2022 Bs 2020) 4. Remove slicers to return back to YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc) Any help on what DAX I need to make this work would really help!
% calculation in a matrix
I have a measure to calculate the percentage one sum over another sum. I am displaying it in a Matrix visual by month but the same % shows for each month. The measure formula is: % Pickup Entered = (DIVIDE(Sum(tbl_KPIMatrix[ShipmentCountPickupComplete]),Sum(tbl_KPIMatrix[ShipmentCountPickup]))) Screen shot below
WoW and MoM Change%
Hi, I have a question regarding calculating WoW and MoM changes. If I put this measure into my graph the numbers are not correct. Dos anybody know if the calculation is correct and if yes how is WoW Change % calculated? Wow = VAR lastWeek = CALCULATE ( SUM ( x), FILTER ( ALL ( 'Date' ), 'Date'[Sortorder Week-Year] = MIN ( 'Date'[Sortorder Week-Year] ) - 1 ) ) RETURN DIVIDE ( SUM ( x) - lastWeek ,lastWeek, 0 ) Wow Change% = VAR lastWeek = CALCULATE ( SUM ( x), FILTER ( ALL ( 'Date' ), 'Date'[Sortorder Week-Year] = MIN ( 'Date'[Sortorder Week-Year] ) - 1 ) ) RETURN DIVIDE ( SUM ( x) ,lastWeek, 0 ) Are these correct because if I put it in my graph it doesnt show the correct numbers. Many thanksCalculate dynamic %change based on date slicer
Hi All, We having Total sales Column in the database and need to find a %change in sales on the period selected. I solve this using the following calculation, This year Sale = calculate(sum([total sale),filter(Item,Item[Timestamp].[Year]=2019)) Last Year Sale = CALCULATE(SUM([total sale]),SAMEPERIODLASTYEAR(Item[Timestamp].[Date])) But I need to find the dynamic %change, which depends on Date slicers selection, we use Relative Date slicer in the report, So if we select last 1 Week on date slicer -> %change if for this selected week with last week, if we select last 1 Month on date slicer -> %change if for this selected Month with last Month, and so on. Please Help, Thanks in Advance.
