cancel
Showing results for
Did you mean:
Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

Hi @dpombal,

What about the formula below?

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

Here is the result output.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

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

Hi ,

your solutions works OK for me except on grand totals

See attached report

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

Community Support Team

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

Hi @dpombal,

What about the formula below?

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

Here is the result output.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

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

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

Community Support Team

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.