Forum Discussion
Populate matrix with last value
Hi,
I need to create a matrix where the empty fields are populated by last value.
I have gotten some part of the way, but now I am stuck and i hope you can help me.
Here is the matrix:
The yellow areas are the areas that need to be populated, but as highlighted there are some descrepencies. And all the totals are wrong. This is due to the measure i have made:
EOL_lost_value =
VAR fiscal_max =
MAX ( 'Fiscal.Date'[Date] )
VAR exp_date =
CALCULATE (
MAX ( 'Service Contr Lines (2)'[Contract_Expiration_Date] ),
ALL ( 'Fiscal.Date' )
)
VAR contr =
CALCULATE (
MAX ( 'Service Contr Lines (2)'[Contract_No] ),
ALL ( 'Fiscal.Date' )
)
VAR last_sum =
CALCULATE (
MAX ( 'Service Contr Lines (2)'[EOL_value] ),
ALL ( 'Service Contr Lines (2)' ),
'Service Contr Lines (2)'[Contract_Expiration_Date] = exp_date,
'Service Contr Lines (2)'[Contract_No] = contr
)
RETURN
IF ( fiscal_max > exp_date, last_sum, [expected_MoM] )
The 'last sum' variable is the one I'm having trouble with.
The 'Service Contr Lines (2)'[EOL_value] is a hardcoded column in the table, and i would like to change it into a dynamic measure instead. It should show the sum for the 'monthly value' in the previous month.
Lastly, i would like to be able to show the actual totals.
Thanks in advance!
I managed to solve this on my own, here are the measures i used:
Month_Val_EOL = // Calculates the sum of the Monthly value for all rows where Exp_date_YN is "yes" // and removes any filter on 'Fiscal.Date' CALCULATE ( SUM ( 'Service Contr Lines (2)'[Monthly value] ), 'Service Contr Lines (2)'[Exp_date_YN] = "yes", ALL ( 'Fiscal.Date' ) )exp_date_EOL = // Returns "True" if the maximum expiration date in 'Service Contr Lines (2)' is less than the fiscal date VAR all_exp_ = CALCULATE ( MAX ( 'Service Contr Lines (2)'[Contract_Expiration_Date] ), ALL ( 'Fiscal.Date' ) ) VAR exp_date_ = all_exp_ < MAX ( 'Fiscal.Date'[Date] ) RETURN IF ( exp_date_, "True", "" )I input the folowing measure into the matrix visual:
EOL_Churn = // Calculates the final result based on whether the expiration date is less than the fiscal date or not VAR Month_value = SUM ( 'Service Contr Lines (2)'[Monthly value] ) RETURN IF ( [exp_date_EOL] = "True", [Month_Val_EOL], Month_value )
3 Replies
- amitchandak
Super User
Anonymous , You should join your date with the date of a date table and use that in the measures and slicers. and then try a measure like
CALCULATE (
lastnonblankvalue('Service Contr Lines (2)'[Contract_Expiration_Date], MAX ( 'Service Contr Lines (2)'[EOL_value] )), all('Date')
)
CALCULATE (
lastnonblankvalue('Service Contr Lines (2)'[Contract_Expiration_Date], MAX ( 'Service Contr Lines (2)'[EOL_value] ))
, filter(all('Date'), 'Date'[Date] <= max('Date'[Date])
)There is an active relationship between the column 'all dates' and the fiscal.date table.
The problem is i do not want to use the EOL_value column, and instead use the 'Monthly value' column. However the monthly value is a column with many smaller values that needs to be summed up for each month, so the MAX function doesn't produce the right result; and i can't get SUM to work either.
My data looks something like this:Contract_No Contract_Expiration_Date Monthly value All_Dates EOL_value A 11-01-2023 00:00 97 01-01-2022 00:00 2396 A 11-01-2023 00:00 97 01-02-2022 00:00 2396 A 11-01-2023 00:00 97 01-03-2022 00:00 2396 A 11-01-2023 00:00 97 01-04-2022 00:00 2396 A 11-01-2023 00:00 97 01-05-2022 00:00 2396 A 11-01-2023 00:00 97 01-06-2022 00:00 2396 A 11-01-2023 00:00 97 01-07-2022 00:00 2396 A 11-01-2023 00:00 97 01-08-2022 00:00 2396 A 11-01-2023 00:00 97 01-09-2022 00:00 2396 A 11-01-2023 00:00 97 01-10-2022 00:00 2396 A 11-01-2023 00:00 97 01-11-2022 00:00 2396 A 11-01-2023 00:00 97 01-12-2022 00:00 2396 A 11-01-2023 00:00 97 01-01-2023 00:00 2396 The EOL_value comes from a summarized table and is then added here using a LOOKUPVALUE function, therefor it is not dynamic, which i would like it to be.
I managed to solve this on my own, here are the measures i used:
Month_Val_EOL = // Calculates the sum of the Monthly value for all rows where Exp_date_YN is "yes" // and removes any filter on 'Fiscal.Date' CALCULATE ( SUM ( 'Service Contr Lines (2)'[Monthly value] ), 'Service Contr Lines (2)'[Exp_date_YN] = "yes", ALL ( 'Fiscal.Date' ) )exp_date_EOL = // Returns "True" if the maximum expiration date in 'Service Contr Lines (2)' is less than the fiscal date VAR all_exp_ = CALCULATE ( MAX ( 'Service Contr Lines (2)'[Contract_Expiration_Date] ), ALL ( 'Fiscal.Date' ) ) VAR exp_date_ = all_exp_ < MAX ( 'Fiscal.Date'[Date] ) RETURN IF ( exp_date_, "True", "" )I input the folowing measure into the matrix visual:
EOL_Churn = // Calculates the final result based on whether the expiration date is less than the fiscal date or not VAR Month_value = SUM ( 'Service Contr Lines (2)'[Monthly value] ) RETURN IF ( [exp_date_EOL] = "True", [Month_Val_EOL], Month_value )
