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!
CALCULATETABLE with SUMMARIZE and Dynamic Filter - Is this possible ?
Hi All, I am learning PowerBI and I am trying to create a table taking 3 columns from another table filtering the project number ( Information of another table) based on a selected value. CurrentProj = SELECTEDVALUE(Proj_CostSummaries[Project_Number]) The Code to create the table is the following : TimeSpent = CALCULATETABLE(SUMMARIZE(Proj_CostSummaries,Proj_CostSummaries[Period_Date],Proj_CostSummaries[TS_Total_Cost_Project_Currency],Proj_CostSummaries[Project_Number]), FILTER(Proj_CostSummaries,Proj_CostSummaries[Project_Number] = [CurrentProj])) I can see all the columns but for some reason the FILTER is not doing anything. If you can help me understand the issue that will be great!
Calculate SUM of allocation over a related table with dynamic year filter
Hello, I am having troubles calculating the sum of Allocation from a related table, based on the year filter selection. I have the following SharePoint lists (I used Excel to simplify): tblAllocation Start_Date End_Date Year_A Allocation_in_Days Employee Project 01-01-21 31-12-21 2021 40 Anna Project 1 01-01-21 31-12-21 2021 135 Linda Project 1 01-01-22 31-12-22 2022 60 Anna Project 1 01-01-22 31-12-22 2022 95 Linda Project 1 01-01-22 31-12-22 2022 15 Hans Project 1 tblProjects Title Start Date End Date Project 1 19-10-20 30-12-22 tblTasks Title Project Start Date Due Date Cost In Days Task 1 Project 1 10-05-21 06-08-21 95 Task 2 Project 1 10-01-22 25-02-22 30 Task 3 Project 1 16-05-22 10-06-22 15 Task 4 Project 1 06-06-22 17-06-22 0 Task 5 Project 1 30-05-22 03-06-22 0 Task 6 Project 1 30-08-21 10-12-21 35 Task 7 Project 1 25-04-22 06-05-22 14 Task 8 Project 1 09-05-22 13-05-22 5 Task 9 Project 1 20-06-22 24-06-22 5 Task 10 Project 1 28-02-22 18-04-22 31 What I would like is to display in a Power BI table the following: Title (tblProjects) Start Date (tblProject) End Date (tblProjects) Cost In Days (tblTasks) Allocation_in_Days (tblAllocation) Project 1 19-10-20 30-12-22 230 345 The relationships are: -tblProjects-tblTasks: 1 to many -tblProjects-tblAllocation: 1 to many And have a filter for the tblTasks Start Date year: Whenever I select a year, the Allocation_in_Days should be calculated accordingly (example: if I select year 2021, the Allocation_in_days should be 175). That is now not the case; How can I do that? Thank you!
Combining Dynamic Measure and Dynamic Attribute Slicer w/ TopN Ranking Slicer
Been working on this for a while. Have not been able to get it. I've been following tutorials such as powerbi.tips as below: https://powerbi.tips/2018/02/crazy-table-gymnastics-part-2-build-support-materials/ and Fourmoo's https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415 The goal here is for end user's to be able to use a TopN slicer along with a Dynamic Attribute Slicer & a Dynamic Measure Slicer. Picture is below: The data model is below: I found that most tutorials recommended putting that Selector table to select Attributes. It didn't work for me really, so I used the Pivoted Data[Attribute] instead for the Dynamic Attribute Slicer. How can we get the Top # of Items table connected to the Dynamic Attribute filter and the Dynamic Measure filter so that end users can filter out the TopN values. Example workbook is attached here https://drive.google.com/open?id=1ijG1ViE7JaxubSF4izQhNHPgRLjqujQi
