I have a challenge with trying maintain a value from a measure that uses a filter to grab a value that is needed for a simple math calculation.
The goal:
For all departments in a table. If the selected year is 2021, go back to December 2020, find the needed value for each department and return it, take that value and subtract it from the current value associated in 2021 and display the difference. I have to do this because of the way data is coming from our ERP. I was able to get it to work creating a custom column in power query, but the issue was that I used hard coded values. All good till data changes in 2020 needed to be made. Needless to say my numbers no longer worked. The solution to grab the value from last year. That way if changes are made it will handle the calc with updated values.
I have gotten it to work for one specific department I then subtract the value from another measure. In this specific case I am using the measure 2020 Sales which apply the returned value to all departments. I am still failing at getting this to work for all departments listed in the visual. Any help is appreciated.
Below are the two associated measure.
December 2020 Sales =
CALCULATE (
SUM ( ‘Account Schedule’[Balance_at_Date_Actual] ),
FILTER (
ALL ( ‘Account Schedule’ ),
‘Account Schedule’[KPI_Code] = “70”
&& ‘Account Schedule’[Date] = DATE ( 2020, 12, 01 )
)
)
Sorry - forum has been unusually busy over the last few days, and a lot of us have been focused heavily on finishing our Data Challenge entries. I’ll be glad to take a look at this one later tonight - looks interesting…
I was working on this one as well at the same time and came up with very different results, since it seems to me that the Balance at Date Actual in both calculations should be calculated as a MAX and not a SUM, since the figures are already cumulative. My #s match the Richmond table in your example, so I think I may be on the right track here, but please confirm:
Prev Dec Sales by Department =
VAR CurrYr = YEAR( TODAY() )
VAR PrevDec = DATE( CurrYr - 1, 12, 01 )
VAR SelDept = SELECTEDVALUE( 'Account Schedule'[Dept.] )
VAR Result =
CALCULATE (
MAX( 'Account Schedule'[Balance_at_Date_Actual] ),
FILTER (
ALL ( 'Account Schedule' ),
'Account Schedule'[KPI_Code] = "70" &&
'Account Schedule'[Date] = PrevDec &&
'Account Schedule'[Dept.] = SelDept
)
)
RETURN
Result
RD Bal Max Today =
VAR SelDept = SELECTEDVALUE( 'Account Schedule'[Dept.] )
VAR MaxDate =
CALCULATE(
MAX( 'Account Schedule'[Date] ),
REMOVEFILTERS( 'Account Schedule'[Date] ),
'Account Schedule'[Date] <= TODAY()
)
VAR RDBal =
CALCULATE(
MAX( 'Account Schedule'[Balance_at_Date_Actual]),
FILTER ( ALL('Account Schedule'),
'Account Schedule'[KPI_Code] = "70" &&
'Account Schedule'[Date] = MaxDate &&
'Account Schedule'[Dept.] = SelDept
)
)
RETURN
RDBal
RD YTD =
[RD Bal Max Today] - [Prev Dec Sales by Department]
I hope this is helpful - full solution file posted below.
Antriksh, thanks so much for your proposed solution. Unfortunately the results were not what I was expecting as suggested by Brian J below which is exactly the result I was expecting to see. I really do appreciate you taking the time to look at my challenge. Thanks Again.
Brian, This is exactly what I was looking for albeit more complexed then I expected, I though it was some very simple thing I was overlooking. My next steps are to review all lines of each measure to get clear on exactly what line of Dax is doing and apply that to the other department measures, and to also mark my dates table as a date table. Once again you have shown how valuable this forum is. I have taken a number your suggestions into our company’s business reporting and will most certainly use this.
Brian, one quick question with your solution. When I applied the measures there are two things that I noticed that are side effects that I am looking at but thought I would ask about.
I noticed when I pick another month outside if the current month the say I look at Feb 2021 the YTD numbers are incorrect in that the numbers shown are from December 2020 when they should be the YTD from the previous month. I am thinking that this is just an conditional statement I can knock out. I should be able do that with no problem.
I also saw that the RD YTD measure shows the correct numbers but does not Total at the bottom of the column. I am looking at that too.
You may have a quick answer, but I am looking into it for sure. I have added a screenshot of both situations and included the pbix file you sent me.
Both of these should be pretty easy fixes. For the first one, I assumed that the RD Max Bal measure would always calculate with the maximum date of TODAY(). However, to change that based on the slicer, you would just need to harvest the value of that slicer and replace the TODAY function with that harvested value.
In terms of fixing totals, that is an extremely common issue with DAX measures. DAX will usually attempt to make a guess (often wrong), but in the case of multiple nested measures will sometimes as in this case just throw a blank, which is the DAX equivalent of ¯_(ツ)_/¯
Enterprise DNA expert @Greg has put together a phenomenal compendium in the DAX Patterns section of the forum on how to fix this problem. Take a look, give it a go, and if you have any problems just give a shout.
Brian,
Worked as expected. I was able to as you suggested harvest the date, and get the correct numbers back whether moving forward or backward on the date slicer. The Dax patterns that Greg posted also gave me what I needed to get the needed totals to display. Now is just some cleanup of my Dax.
Thanks again. I am now able to move this into production.
