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!
How to show the percentage change of sales from the previous month instead of the cumulative percent
Hi I am a Junior BI Developer. This is training data i created to simulate what i am trying to do. Which is to show just the Monthly percentage change in sales for the current month. I have a solution but from my image below PowerBI keeps giving me the total percentage change highlighted in green. I want the final value in the Alt MoM Sales % or the MoM Sales % Column which is 33.33% highlighted in red. Formula Used is below: Total Sales = SUMX('Fruit Sales', 'Fruit Sales'[Sales]) /* This is for previous months sales */ Last PM Sales = CALCULATE([Total Sales], PREVIOUSMONTH('Fruit Sales'[Date])) MoM Sales = [Total Sales] - [Last PM Sales] MoM Sales % = DIVIDE([MoM Sales], [Last PM Sales]) /*An alternative formula to find the previous month's sales */ Alt PM Sales = CALCULATE([Total Sales], PARALLELPERIOD('Fruit Sales'[Date], -1, MONTH)) Alt MoM Sales = [Total Sales] - [Alt PM Sales] Alt MoM Sales % = DIVIDE([MoM Sales], [Alt PM Sales])
How to calculate percentage difference across columns in a matrix?
Questions Using DAX, how do you calculate the percentage difference across columns in a Power BI matrix? Can DAX do this without hardcoding the column value? Use Case For example, say, I want to create a matrix of Sum of Revenue against Week as Rows and Year as columns in a matrix table. See Example Data below. I want to compute percentage difference of Sum of Revenue by Year (column) for each Week (row). See Expected Result below. How could I compute that percentage difference without hardcoding Year? My data source always gives year-to-date (YTD) data for the last 3 years of data. And I am hoping to avoid having to update the hardcoded year when the new year roles over. See Background below if interested in seeing how this is done using a PivotTable in Excel. Example Data Week Year Revenue 1 2020 6800 2 2020 7300 3 2020 7350 1 2021 7260 2 2021 8300 3 2021 8360 1 2022 8300 2 2022 9100 3 2022 8900 Expected Result Week 2020 2021 2022 1 6.76% 14.33% 2 13.70% 9.64% 3 13.74% 6.46% Total 11.52% 9.95% Background I am an Excel user familiar with PivotTables. To do the same, I would pivot Sum of Revenue on Week as Rows and Year as Columns. Then change the field values of Sum of Revenue to Show Values As to % Difference From for Year as the base field and (previous) as the base item. I am trying to translate this logic in an Excel PivotTable to a Power BI matrix and am getting stuck. It appears that Power BI doesn't have the equivalent menu options. So I am guessing I have to use DAX to get this done. Excel PivotTable Menu Options Power BI Matrix Menu OptionsPercentage Difference Shows Minus value in plus
Hi Everyone I hope you all are fine. So reference to the visuals. I have calculated a % diff between Budgeted sale 2021 and actual sale 2021. It shows the correct percentage values but in red box it should mention the percentage in minus not in positive. Kindly let me know If someone can help me out. Thanks in advance.
