cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevinarias Frequent Visitor
Frequent Visitor

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.

11 REPLIES 11
Super User
Super User

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.
kevinarias Frequent Visitor
Frequent Visitor

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
Super User

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.
kevinarias Frequent Visitor
Frequent Visitor

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
Super User

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.
Sean Super Contributor
Super Contributor

Re: earlier

@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
Super User

Re: earlier

good point Sean - brain freeze. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
kevinarias Frequent Visitor
Frequent Visitor

Re: earlier

Hi @MattAllington

 

I tried the measure, but it doesn't work

 

Captura8.PNG

 

Thanks.

 

Regards.

 

kevinarias Frequent Visitor
Frequent Visitor

Re: earlier

Hi @Sean

Thanks for your answer.

 

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

 

Captura9.PNG

 

Regards.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 116 members 1,614 guests
Please welcome our newest community members: