Reply
Frequent Visitor
Posts: 6
Registered: ‎05-19-2018

earlier

Hi.

 

I have a problem for calculate a max date in a report.

I need to calculate max date per category in the selected range of dates.

I am using the following formula: 

 

MaxDateGroup = CALCULATE(
                                             MAX(Siembra[DateGroup])
                                               ;FILTER(Siembra;Siembra[_Año Semana]=EARLIER(siembra[_Año semana]
                                               )

                                             )

                                           )

 

But it doesn't work

 

Captura.PNG

 

Thanks for your help.

Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: earlier

Given you are using earlier, I guess this is a calculated column (or it returns an error). I would write a measure. 

 

=CALCULATE(max(table[datecolumn]),all(table[categoryColumn]))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor
Posts: 6
Registered: ‎05-19-2018

Re: earlier

Hi @MattAllington thanks for your answer.

 

I tried with the suggested formula but it doesn't work

 

SuggestedMeasure = CALCULATE(max(Siembra[DateGroup]);all(Siembra[BedIdentifierId]))

 

Cause the result of this, show me maxdate of the all data

 

 

Captura6.PNG

 

But i need the max date in the selected range, i tried with this another formula

 

CALCULATE( MAX(Siembra[DateGroup]);FILTER(ALL(Fecha);Fecha[_Año Semana]<=MAX(Fecha[_Año Semana])))

But this, doesn't replicate the max date for each row 

 

Captura7.PNG

The result that i expect, is the max date replicated for each row in the selected range.

Captura5.PNG

 

This is an example of the expected goal.

 

 

 

Captura3.PNG

 

-------------------------------------------------------------------------------Captura4.PNG

 

Thanks for your help.

 

Regards.

 

 

 

Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: earlier

I can see you have tried hard to explain the issue, but unfortunately I don’t follow. No doubt a combination of this not being in English and me not seeing the data Model.  Maybe you can work it out yourself if you understand what you need to do.   The measure I shared removed the filter from the column specified. If his is not the right column, then change the column to the category needed. If you want to keep the slicer applied, then use ALLSELECTED instead. I have an article on ALL here https://exceleratorbi.com.au/the-all-function-in-dax/

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor
Posts: 6
Registered: ‎05-19-2018

Re: earlier

Hi @MattAllington

Let me to explain myself better.
I currently have categories with different dates over time. The need is that based on the selected time range, calculate the maximum date per category.

 

Rango1.PNG

 

Resultado1.PNG



This is the new range with the new expected result

 

 Rango2.PNG

 

Resultado2.PNG

 

 

Thanks.

 

Regards.

Highlighted
Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: earlier

OK, so try my suggestion from before

 

=CALCULATE(max(table[datecolumn]),allselected(table[categoryColumn]))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Posts: 2,146
Registered: ‎08-11-2015

Re: earlier

[ Edited ]

@kevinarias

 

This should work as either Measure or Column

 

Max Date Per Category =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )

 

Good Luck! Smiley Happy

Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: earlier

good point Sean - brain freeze. 

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Frequent Visitor
Posts: 6
Registered: ‎05-19-2018

Re: earlier

Hi @MattAllington

 

I tried the measure, but it doesn't work

 

Captura8.PNG

 

Thanks.

 

Regards.

 

Frequent Visitor
Posts: 6
Registered: ‎05-19-2018

Re: earlier

Hi @Sean

Thanks for your answer.

 

I tried with the formula, but it doesn't work

 

Captura9.PNG

 

Regards.