cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrehawari Regular Visitor
Regular Visitor

Flagging Latest End Date Available each month to 1

Hi, I tried to create DAX measure that will flag every end of latest available date each month to one. Later on, I am going to use this measure as visual level filter. The illustration is given in the table below.

For instance: 

a.  In January 2018, the latest available date is 10 Jan 2018, so that row will be flagged to 1

b.  In February 2018, the latest available date 28 February 2018, so that row will be flagged to 1

c. in March 2018, the latest available date is 2 march 2018, so that row will be flagged to 1.

d.  the rest of the rows will be flagged to 0

 

I was thinking to use Calculate function combined with ENDOFMONTH Function like 

FlagEOFMONTH = CALCULATE(1,FILTER(MyTable,ENDOFMONTH(MyTable[DateNational]))), but it does not works

Any idea about how to generate correct dax measure ?

DateValueFlag
1-Jan-18200
2-Jan-18200
3-Jan-18200
4-Jan-18120
5-Jan-18170
6-Jan-18120
7-Jan-18150
8-Jan-18110
9-Jan-18200
10-Jan-18181
1-Feb-18150
2-Feb-18200
3-Feb-18150
4-Feb-18110
5-Feb-18140
6-Feb-18140
7-Feb-18170
8-Feb-18140
9-Feb-18190
10-Feb-18180
11-Feb-18160
12-Feb-18140
13-Feb-18100
14-Feb-18110
15-Feb-18170
16-Feb-18190
17-Feb-18100
18-Feb-18120
19-Feb-18180
20-Feb-18160
21-Feb-18150
22-Feb-18180
23-Feb-18150
24-Feb-18100
25-Feb-18150
26-Feb-18160
27-Feb-18140
28-Feb-18191
1-Mar-18170
2-Mar-18181

 

Thanks

Andre

1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor
Super Contributor

Re: Flagging Latest End Date Available each month to 1

Hi @andrehawari

 

ENDOFMONTH function wont work as your latest date for the month could be any date, not necessarily to be month end.

 

Use the below formula t create a calculated column ( Not measure):

 

Monthend = IF(CALCULATE(MAX(Table1[Date]),FILTER(Table1,MONTH(Table1[Date])=MONTH(EARLIER(Table1[Date]))))=Table1[Date],1,0)

 

Monthend Flag.PNG

 

Thanks

Raj

View solution in original post

2 REPLIES 2
rajendran Super Contributor
Super Contributor

Re: Flagging Latest End Date Available each month to 1

Hi @andrehawari

 

ENDOFMONTH function wont work as your latest date for the month could be any date, not necessarily to be month end.

 

Use the below formula t create a calculated column ( Not measure):

 

Monthend = IF(CALCULATE(MAX(Table1[Date]),FILTER(Table1,MONTH(Table1[Date])=MONTH(EARLIER(Table1[Date]))))=Table1[Date],1,0)

 

Monthend Flag.PNG

 

Thanks

Raj

View solution in original post

andrehawari Regular Visitor
Regular Visitor

Re: Flagging Latest End Date Available each month to 1

Perfect ! Thanks a lot Raj Smiley Happy

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: 126 members 1,539 guests
Please welcome our newest community members: