cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dpombal Member
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

 1_dim_banks.PNG

 

 

 

 

 

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

 

2_fact_snap.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 .

 

 

3_date_update_BEFORE_Filter.PNG 

 

 

 

 

 

 

 

 

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)

4_Desired_Output_for_15062011.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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)

5_Desired_Output_for_20012011.PNG

 

 

 

 

 

 

 

 

 

 

Kind Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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.

measure2.PNG

 

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

result.PNG

 

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.
dpombal Member
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

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

 

 

 

 

 

 

image001.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regards

Community Support Team
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.

measure2.PNG

 

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.
dpombal Member
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

 

6 why mixing measures doesnt work.PNG

 

 

 

Regards

 

Community Support Team
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.