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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
efilipe
Helper IV
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
Vvelarde
Community Champion
Community Champion

@efilipe

 

Try with this calculated column:

 

ld.png

 

Regards

 

Victor




Lima - Peru

View solution in original post

15 REPLIES 15
Vvelarde
Community Champion
Community Champion

@efilipe

 

Hi, Try with:

 

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

 

Regards

Victor




Lima - Peru

Not really 😞

Vvelarde
Community Champion
Community Champion

@efilipe

 

In your sample table what is the most recent day?

 

VV

 

 

 

 




Lima - Peru

09/11/2017 🙂

DD/MM/YYYY

Vvelarde
Community Champion
Community Champion

@efilipe

 

Try with this calculated column:

 

ld.png

 

Regards

 

Victor




Lima - Peru

@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)

Vvelarde
Community Champion
Community Champion

Hi, try with:

 

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

 

Regards

 

Victor




Lima - Peru

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

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

Vvelarde
Community Champion
Community Champion

@efilipe

 

Are using a calculated column?

 

VV




Lima - Peru

@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

Vvelarde
Community Champion
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

@Vvelarde I am

Thanks for being awsome! 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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