Filter Command

[lmorris]

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 )
)
)

RD Sales YTD =
CALCULATE (
SUM ( ‘Account Schedule’[Balance_at_Date_Actual] ) - [December 2020 Sales],
FILTER ( ‘Account Schedule’, ‘Account Schedule’[KPI_Code] = “70” )

 

[amitchandak]

@lmorris , I think you should use time intelligence for that.

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

additional filter for last month start date

Date[Date] = eomonth(today(),-2)+1

last month first Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),

filter(Date, Date[Date] = eomonth(today(),-2)+1 )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[lmorris]

@amitchandak , Thanks for your help on this. I get what you are suggesting. Let me add a little more detail of my challenge. The main issue is this I am pulling the data from our ERP and grabbing data associated with an account schedule. For each month of the year 2020 are entries in the fact table that are dated the first day of each month. Below is a sceenshot. The issue I am stuck with is I have to take the December Balance_at_date_acutal number and subtract it from the Balance_at_date_actual number to get the correct ytd number due to last year not being closed. With the year not being closed the data coming out of our ERP is continuing to aggregate numbers into 2021. 

Your solution is putting me on the right path, as long as I am in January 2021. When we move into February it will only go back 1 month and pick up January numbers. I guess I could use KEEPFILTERS. 

If you look at the screenshot below all the month to day numbers match the year to date numbers because of the following calculated column code I created which is a horrible approach but I was pressed for time and need to get a better grip on filter context. 

Your thoughts? I have to be very close maybe too close to see the issue.

 calculated column formula

--------

= Table.AddColumn(#"Added Conditional Column", "Custom",
each if Date.IsInCurrentYear([Date]) and [#"Dept."] = "New" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 15255778.51 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Used" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 2755501.16 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Systems" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 37904650.20 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Service" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 13621354.73 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Parts" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 9355203.38 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Rental" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 7268223.52 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Delivery" and [KPI_Code] = "10" then [Balance_at_Date_Actual] - 878158.35 else

if Date.IsInCurrentYear([Date]) and [#"Dept."] = "New" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 8661733.73 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Used" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 1888680.56 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Systems" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 37442592.01 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Service" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 7812799.22 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Parts" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 4974784.50 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Rental" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 5028506.97 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Delivery" and [KPI_Code] = "30" then [Balance_at_Date_Actual] - 736693.52 else

if Date.IsInCurrentYear([Date]) and [#"Dept."] = "New" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 4776865.89 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Used" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 141234.01 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Systems" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 237069.95 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Service" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 4313801.47 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Parts" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 3400901.83 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Rental" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 1556174.71 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Delivery" and [KPI_Code] = "50" then [Balance_at_Date_Actual] - 83962.40 else

if Date.IsInCurrentYear([Date]) and [#"Dept."] = "New" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 632084.85 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Used" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 129850.00 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Systems" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 121868.11 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Service" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 664816.17 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Parts" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 458398.73 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Rental" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 354451.50 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Delivery" and [KPI_Code] = "70" then [Balance_at_Date_Actual] - 23287.23 else

if Date.IsInCurrentYear([Date]) and [#"Dept."] = "New" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 981979.49 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Used" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 133051.22 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Systems" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 65493.04 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Service" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 793728.26 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Parts" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 448227.32 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Rental" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 310725.09 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Delivery" and [KPI_Code] = "90" then [Balance_at_Date_Actual] - 25945.20 else

if Date.IsInCurrentYear([Date]) and [#"Dept."] = "New" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 203114.55 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Used" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 462685.37 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Systems" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 37627.09 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Service" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 36209.61 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Parts" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 72891.00 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Rental" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 18365.25 else
if Date.IsInCurrentYear([Date]) and [#"Dept."] = "Delivery" and [KPI_Code] = "110" then [Balance_at_Date_Actual] - 8270.00 else [Balance_at_Date_Actual])

Data Model