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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Identify last day

Hi guys,

I have a date table being constructed like:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2017;5;1); TODAY());
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"DaysOld"; VALUE(TODAY() - [Date]);
)

I'd like to add a column with 1 (true) or 0 (false) identifying the date as the most recent (MAX). Looks simple, but I cant get it right...

Thanks!

1 ACCEPTED SOLUTION
Community Champion

@efilipe

Try with this calculated column:

Regards

Victor

Lima - Peru
15 REPLIES 15
Community Champion

@efilipe

Hi, Try with:

LastDay = IF('Date'[Date]=MAX('Date'[Date]),1,0)

Regards

Victor

Lima - Peru
Helper IV

Not really 😞

Community Champion

@efilipe

In your sample table what is the most recent day?

VV

Lima - Peru
Helper IV

09/11/2017 🙂

Helper IV

DD/MM/YYYY

Community Champion

@efilipe

Try with this calculated column:

Regards

Victor

Lima - Peru
Helper IV

@Vvelarde

I had to alter the filter, but I'm getting an error. Any idea? 🙂

I want to get the last day when SO is Android. 🙂

LastDayAndroid = IF('Date'[Date] = Max('Stores'[Date]) && 'Stores'[OS] = 'Android';1;0)

Community Champion

Hi, try with:

LD = If(Store[Date]=Max('Date'[Date]) && Store[Store]="Android";1;0)

Regards

Victor

Lima - Peru
Helper IV

It says something about not being able to get a unique result

Helper IV

Nope. It looks like it can't get an unique value

Community Champion

@efilipe

Are using a calculated column?

VV

Lima - Peru
Helper IV

@Vvelarde i'm trying to resolve on the Stores table with M function. Any idea how to solde the error:

Expression.Error: Não é possível converter o valor #date(2017, 8, 😎 para o tipo List.
Detalhes:
Value=08/08/2017
Type=Type

if [Date] = List.Max([Date]) && [OS] = "Android" then 1 else 0

Community Champion

@efilipe

The quick way is share a file without confidential data.

you can share here or by Private Message.

Regards

Victor

Lima - Peru
Helper IV

@Vvelarde I am

Helper IV

Thanks for being awsome! 🙂

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors