I’m interested in expanding upon the concept to getting a Previous value with Time intelligence found here in a previous Enterprise DNA forum post Using variables to get previous values - DAX Calculations - Enterprise DNA Forum This works pretty well for me but the grouping and ordering needs to be based on more than one field and unfortunately I’m new to DAX. See image below where I concatenated the 3 fields into a calculated column called SM - That works fine for me. I’m needing to group by SUFFIX and rollup the RA Amount and the Premium Amount by Suffix, StepNumber and ModelName. RA Amount and Premium Amount are measures based on an Amount column. As you can see there are multiple measures and a ‘Last Amount’ for each measure. But if I add SUFFIX and/or StepNumber and/or ModelNumber to the table it breaks. This first one is filtered by SUFFIX = ‘CURRENT’
Ideally it should only pull last values from the current record SUFFIX value - this is the other thing I haven’t been able to do see screenshot below. I realize this is a bit different topic - I’ve been able to categorize the indexes but I’ve been unable to combine the concepts.
Hi @SMMCDONNELL, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.
Attached is the corresponding PBIX file - since I first submitted this question I worked with others to get a partial solution. We’ve created a Groupby table then concatenated several columns to create a Key then created Index 1. I’m not sure this is the correct way to solve this. The part that is not working is getting last value only from current SUFFIX field. That is why the filter by suffix tab in the reports works and the second tab where it is showing all SUFFIX’s is not working. Thanks SMCDONNELL
The expected outcome would be similar to this however - see the transition between the CURRENT and Indirect and the INDIRECT and LOOKUP Suffix values? The INDIRECT LAST RA Amount value should NOT be pulling from CURRENT RA Amount it should be blank at the beginning because there is no previous within INDIRECT and the same with the LOOKUP section LAST RA Amount 2 should NOT be pulling from INDIRECT the first previous should be blank. See the red marks in the image below. Please let me know if this doesn’t make sense. Each SUFFIX values and totals should be self contained. Thanks in advance for looking at this.
Thanks for posting this quesiton and providing detailed explanation. This is quite an interesting problem. I have worked on Last Prem and it seems that it is working as per your requirements. I have used variables instead of EARLIER function as working with variables is quite straigh-forward. Please see below snapshot of solution where I have done multiselection in filter and it is working fine.
@hafizsultan - Thanks again - I applied your suggested DAX for the LAST values that I’m calculating and it is working great! Thank you I will mark as a Solution. After looking at your DAX the following alternative was suggested to me as more efficient. I would like your input into this as I know it is fewer lines of code but I’m not sure if one is better than the other?
Pre Prem Amt = CALCULATE(maxx(filter(Result2,Result2[ResultsReportingSubset_LOOKUP]=“Premium”),[Activity Amt]),
Excellent work and now you got 2 solutions . The second solution which you mentioned is quite a smart solution. I am 100% sure that there will be more solutions for this problem which can be more efficient as everyone’s thought process is different.
As both solutions are giving same result, so you can select the measure which is taking less time. You can do this by using performance analyzer and refreshing your visual. In your case, I have tested by putting both measures separately and following are the results.
. The second solution which you mentioned is quite a smart solution. I am 100% sure that there will be more solutions for this problem which can be more efficient as everyone’s thought process is different.
