cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 42 members 994 guests
Please welcome our newest community members: