Get value of max date for each value of a dimension Filter calculate issue

[dpombal]

Hi all,

I have a bank dimension table like this

Cod_bank	Desc_bank
30	BANK_A
61	BANK_B
182	BANK_C
198	BANK_D

 

My question is regarding fact table (with snapshots)

Cod_bank	date_update	Registers
30	02/01/2011	107.449
30	19/01/2011	123.572
30	23/01/2011	123.703
30	30/01/2011	123.958
30	06/02/2011	124.670
61	15/06/2011	2.837
182	23/01/2011	436.329
182	30/01/2011	435.971
182	06/02/2011	441.024
198	16/01/2011	45
198	23/01/2011	53
198	30/01/2011	74
198	06/02/2011	85
198	20/02/2011	80
198	13/03/2011	79
198	27/03/2011	77

My requirement is to have a date_update filter and get the number of Registers of the max date_update by Bank.

DAX Measures

max_date = max(Facts[date_update])

num_registers = SUM(Facts[Registers])

Registers on max date for this bank = CALCULATE([num_registers];

                                                                          FILTER(Facts;Facts[date_update]=[max_date]))

Filter is failing due to context, I need max_date for Each bank before date selected

Report configuration with date_update, Before filter .

 

Desired output is the number of Registers in the max available date Before selected, for Each bank

If Selected 15/06/2011 (June 15 2011)

Des_bank	date_update	Registers
BANK_A	06/02/2011	124.670
BANK_B	15/06/2011	2.837
BANK_C	06/02/2011	441.024
BANK_D	27/03/2011	77

Current report for 15/06/2011 (June 15 2011)

If Selected 20/01/2011 (January 20 2011)

Des_bank	date_update	Registers
BANK_A	06/02/2011	123.572
BANK_D	27/03/2011	45

Current Report for Selected 20/01/2011 (January 20 2011)

Kind Regards

[v-piga-msft]

Hi @dpombal,

Do you want to get the ouput below?

If this is your desired output, please refer to the formula below.

Measure = 
CALCULATE (
 SUM ( fact_[Registers] ),
 FILTER ( 'fact_', 'fact_'[date_update] = MAX ( 'fact_'[date_update] ) )
)

If you still need help, please share your desired output so that we could help further on it.

Best  Regards,

Cherry

[dpombal]

Hi ,

your solutions works OK for me except on grand totals

See attached report

https://drive.google.com/file/d/10BliwwN3ZAd8PibCGkz7k07fZDPUn6fK/view?usp=sharing

Something like

IF HASONEVALUE BANK

THEN CURRENT behaviour of Measure

ELSE in totals SUM PREVIOUS ROWS with registers on max date available for each bank

Regards

[v-piga-msft · Accepted solution]

Hi @dpombal,

What about the formula below?

Measure 2 = SUMX('DIM_BANKS',[Measure])

Here is the result output.

Best Regards,

Cherry

[dpombal]

Hi, I am surprised the approach using 

below 2 measures works fine,

Measure_aux = CALCULATE ( [sum_registers],
FILTER ( Facts, Facts[date_update] = MAX ( Facts[date_update] ) )
)

Measure 2 = SUMX('DIM_BANKS',[Measure_aux])

However, after mixing both in a single measure...something strange happens

Measure3 = SUMX('DIM_BANKS',
CALCULATE (
[sum_registers],
FILTER ( Facts, Facts[date_update] = MAX ( Facts[date_update] ) )
)
)

The sum is not correct....I can't figure out why this is happening and the order of evaluation... but this is the strange result

Regards

[v-piga-msft]

Hi @dpombal,

I'm afraid that you cannot mix the formulas like that. If you nest a measure in measure, it may cause incorrect result.

For the reason, you could have a reference of this blog.

Best Regards,

Cherry