Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kevinarias
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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

 

 

 

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

OK, so try my suggestion from before

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi @MattAllington

 

I tried the measure, but it doesn't work

 

Captura8.PNG

 

Thanks.

 

Regards.

 

@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

Hi @Sean

Thanks for your answer.

 

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

 

Captura9.PNG

 

Regards.

Sean
Community Champion
Community Champion

@kevinarias

Post some sample data in format that can be easily copied into PBI.

Make sure to include all columns/tables involved and all date columns...

Which Date Column is used to relate to your Calendar/Date table?

And which Date do you use in the Slicer?

good point Sean - brain freeze. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.