Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone!
Been looking into dax solutions for my scenario but can't seem to get the right for my spefic scenario. For my report, I currently have a data set like this: (below is just a sample but for scale my duration is for around 5 years and with 10 locations)
Date | Location A | Location B | Total |
1/1/2020 | 0.300 | 0.325 | 0.62500 |
1/2/2020 | 0.600 | 0.650 | 1.25000 |
1/3/2020 | 0.900 | 0.975 | 1.87500 |
1/4/2020 | 1.200 | 1.300 | 2.50000 |
1/5/2020 | 1.500 | 1.625 | 3.12500 |
1/6/2020 | 1.800 | 1.950 | 3.75000 |
1/7/2020 | 2.100 | 2.275 | 4.37500 |
1/8/2020 | 2.400 | 2.600 | 5.00000 |
1/9/2020 | 2.700 | 2.925 | 5.62500 |
1/10/2020 | 3.000 | 3.250 | 6.25000 |
1/11/2020 | 3.300 | 3.575 | 6.87500 |
1/12/2020 | 3.600 | 3.900 | 7.50000 |
1/13/2020 | 3.900 | 4.225 | 8.12500 |
1/14/2020 | 4.200 | 4.550 | 8.75000 |
1/15/2020 | 4.500 | 4.875 | 9.37500 |
1/16/2020 | 4.500 | 5.200 | 9.70000 |
1/17/2020 | 4.500 | 5.525 | 10.02500 |
1/18/2020 | 4.500 | 5.850 | 10.35000 |
1/19/2020 | 4.500 | 6.175 | 10.67500 |
1/20/2020 | 4.500 | 6.500 | 11.00000 |
Scenarios are the ff:
1. I have two locations with a total savings goal given their own set of target date:
Location A Target date: 1/15/2020 with $4.5 savings
Location B Target date: 1/20/2020 with $6.5 savings
2. Table shows the somewhat history log daily growth of each location until it finally got to it's goal on their respective dates. So as you can see, Location A is still showing 4.5 after 1/15/2020 because it has reached it's goal
3. I used the total column to use in area chart to see the day to day increase until the final target date for all locations. Used MAX value rather than SUM since it's day to day growth
4. I created another bar chart to show now each locations progress. X- axis using the savings. Y axis using the Location. For do this I duplicate the same data set, removed the total then unpivot the locations so it would be in 2 columns: Location and Savings
What I want to achieve is when I click on a certain period in the area chart, let's say 1/17/2020. The bar chart will move along it so Location A will have full bar since it's total goal is already set, but for Location B it should only show the 5.525 as the highlighted portion since the Goal of 6.5 is still not yet met on that day. Same goes with using slicers dates and not just the highlighting part
Sorry if my explanation is a bit confusing. Am really open to using dax or changing my approach on the tables
Solved! Go to Solution.
It's a bit hard to understand your problem.
General Advice:
1. always unpivot your data before you start in DAX
2. work with deltas in your columns (instead of cumulatives)
Day Savings =
VAR __filter = FILTER('Table', 'Table'[Location] = EARLIER('Table'[Location]) && 'Table'[Date] = EARLIER('Table'[Date]) -1)
VAR __cumSavingsPreviousDay = MAXX(__filter, 'Table'[**bleep** Savings])
RETURN
'Table'[**bleep** Savings] - __cumSavingsPreviousDay
However neither should represent a problem in your case. For clarity I made a screenshot of a matrix and a slicer and they give the result you want (as I understand it)
It's a bit hard to understand your problem.
General Advice:
1. always unpivot your data before you start in DAX
2. work with deltas in your columns (instead of cumulatives)
Day Savings =
VAR __filter = FILTER('Table', 'Table'[Location] = EARLIER('Table'[Location]) && 'Table'[Date] = EARLIER('Table'[Date]) -1)
VAR __cumSavingsPreviousDay = MAXX(__filter, 'Table'[**bleep** Savings])
RETURN
'Table'[**bleep** Savings] - __cumSavingsPreviousDay
However neither should represent a problem in your case. For clarity I made a screenshot of a matrix and a slicer and they give the result you want (as I understand it)
Thank you for the guidelines, I'll take note of that for futureu data sets as well.
EDIT: Sorry I didn;t notice that you are referring to making Dax Columns and not measure. All is good now. Thank you so much
As for the Dax formula, is it possible to show me the Formula bar as well? I tried following your formula above but for the "EARLIER" function, it won't allow me to use a table column. There are no table columns in the suggested fields after EARLIER then when I just manually type it, it won't take it. Let me know if i'm reading the formula wrong