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!
Slicer to create a dynamic visual
HELP please! How can I create a dynamic visual with the details from kWh after tech and have toggles in a slicer column 4 (LED/Controls, Refrig Opt, etc)? I would like to have two visuals, a static one with the details Category and kWh w/o tech and a dynamic visual with Category and kWh after tech , and to be able to change it using a slicer. Many thanks.
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!
Power BI Query Dynamic Filter
HI, Hope you are doing well. I have a small query in Power BI. Data source: Oracle SQL in import mode. My customer wants to create a filter based on a date that only shows the data for yesterday and today's date. So for example : If we have a date column from 01/01/2019 ----31/12/2020 The customer should see only 31/12/2020 and the day before ( 30/12/2020) by default. And after that, if they want they can move the filter and changes the date to see the historical data. (The data from previous years: 2019) I managed to make that filter by the logic: Max date and Max Date-1 but for a moment it is static and not dynamic. The filter should be on the creation date. Any idea how to achieve this? ThanksCombining 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
