Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have a simple problem to solve but no easy way to solve it. I have a dataset where values are given for a period of time for each year-month period. I also have various dimensions of these values [Dimension 1], [Dimension 2], etc. What I need is to be able to create an index chart which shows based on these parameters the % change of the value over the historical period based on the time period selection I make. I should be able to use the slicers to choose the start and end year, specific months for which to sum up the value and all the dimensions.
The result should look like this:
Year | New Cases | IndexNewCases |
2015 | 10107 | 100% |
2016 | 12543 | 124% |
2017 | 10164 | 101% |
2018 | 9975 | 99% |
2019 | 3128 | 31% |
I have the following formula which almost works:
IndexNewCases = divide( sum('Data'[New Cases]); calculate(sum('Data'[New Cases]); filter ( ALLEXCEPT('Data';'DIM_Region'[Region];'DIM_BU'[BU];'DIM_LOB'[Line of Business];'DIM_Type'[Type];'Data'[Month]); 'Data'[Year]=2015 ) ) )
IndexNewCases = VAR MinYear = MIN('Data'[Year]) RETURN divide( sum('Data'[New Cases]); calculate(sum('Data'[New Cases]); filter ( ALLEXCEPT('Data';'DIM_Region'[Region];'DIM_BU'[BU];'DIM_LOB'[Line of Business];'DIM_Type'[Type];'Data'[Month]); 'Data'[Year]=MinYear ) ) )
Year | New Cases | IndexNewCases |
2015 | 10107 | 100% |
2016 | 12543 | 100% |
2017 | 10164 | 100% |
2018 | 9975 | 100% |
2019 | 3128 | 100% |
Year | Month | New Cases | IndexNewCases |
2015 | January | 1053 | 10% |
2016 | January | 1217 | 12% |
2017 | January | 1016 | 10% |
2018 | January | 792 | 8% |
2019 | January | 726 | 7% |
I have tried to add MIN formula to the filter settings but it doesn't work. My attempt above to solve it through a variable also failed.
Any help will be very much appreciated!
Cheers!
Solved! Go to Solution.
var __currentCases = [# New Cases] var __minimumYear = SELECTEDVALUE( 'Minimum Years'[Minimum Year] ) var __currentCasesForMinimumYear = CALCULATE( [# New Cases], 'Calendar'[Year] = __minimumYear ) RETURN DIVIDE( __currentCases, __currentCasesForMinimumYear )
The logic above keeps all filters on the 'Calendar' table apart from the year. The year is shifted to the selected value. Please note this will work with month, quarter and other date entities that are not specifically tied to a given year. So, for instance, if you slice by month "2018-JUN", the measure will return BLANK because there is no "2018-JUN" in the year 2019, for instance. But if you slice by "JUN" itself, you'll get what you need because the shifted year does have data for "JUN" and the period selected/filtered will be June 2019.
With DAX it's all about knowing how your data is structured and how CALCULATE applies filters.
I would also suggest that you give the tables and entries in your tables PROPER NAMES that will make it easy for the user to distinguish among them and know what they are for just by a quick look. For instance, I'd rename the 'Minimum Year' parameter table to 'Base Year'. Naming is of utmost importance when building understandable and complex models.
Best
Darek
-- Let's say you've got a Calendar (as you should) -- in your model that covers all the full years -- for which there is at least one date in any of -- your tables and the Calendar is marked as a -- DATE TABLE. You should make a relationship
-- from 'Calendar'[Date] to the date field in
-- your fact table, 'Data'. -- You should also have a disconnected table, 'Minimum Years', -- with a column that will be used for a slicer. -- Say the col name is 'Minimum Years'[Minimum Year] and its data type -- should be the same as the 'Calendar'[Year]'s column. -- First of all, you should create basic measures. -- Here's one you should have defined upfront: [# New Cases] := SUM( 'Data'[New Cases] ) -- Second, here's probably the calc you're after: var __currentCases = [# New Cases] var __minimumYear = SELECTEDVALUE( 'Minimum Years'[Minimum Year] ) var __currentCasesForMinimumYear = CALCULATE( [# New Cases], 'Calendar'[Year] = __minimumYear, ALL( 'Calendar' ) ) RETURN DIVIDE( __currentCases, __currentCasesForMinimumYear )
Best
Darek
Hi Darek,
First of all, thanks a million! As you suggested I did create a separate calendar for Minimum Years and it works beautifully:
But I do have one more question: the solution does not work when I also try to specify months within a year - to be able to compare the same periods across several years. It starts taking % of the total year result for the minimum year:
What shall I do in this case? Shall I add months to my Minimum Year table? Or is there another way?
Thanks!
var __currentCases = [# New Cases] var __minimumYear = SELECTEDVALUE( 'Minimum Years'[Minimum Year] ) var __currentCasesForMinimumYear = CALCULATE( [# New Cases], 'Calendar'[Year] = __minimumYear ) RETURN DIVIDE( __currentCases, __currentCasesForMinimumYear )
The logic above keeps all filters on the 'Calendar' table apart from the year. The year is shifted to the selected value. Please note this will work with month, quarter and other date entities that are not specifically tied to a given year. So, for instance, if you slice by month "2018-JUN", the measure will return BLANK because there is no "2018-JUN" in the year 2019, for instance. But if you slice by "JUN" itself, you'll get what you need because the shifted year does have data for "JUN" and the period selected/filtered will be June 2019.
With DAX it's all about knowing how your data is structured and how CALCULATE applies filters.
I would also suggest that you give the tables and entries in your tables PROPER NAMES that will make it easy for the user to distinguish among them and know what they are for just by a quick look. For instance, I'd rename the 'Minimum Year' parameter table to 'Base Year'. Naming is of utmost importance when building understandable and complex models.
Best
Darek
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
17 | |
16 | |
15 |
User | Count |
---|---|
50 | |
26 | |
21 | |
17 | |
16 |