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

 Date Value Flag 1-Jan-18 20 0 2-Jan-18 20 0 3-Jan-18 20 0 4-Jan-18 12 0 5-Jan-18 17 0 6-Jan-18 12 0 7-Jan-18 15 0 8-Jan-18 11 0 9-Jan-18 20 0 10-Jan-18 18 1 1-Feb-18 15 0 2-Feb-18 20 0 3-Feb-18 15 0 4-Feb-18 11 0 5-Feb-18 14 0 6-Feb-18 14 0 7-Feb-18 17 0 8-Feb-18 14 0 9-Feb-18 19 0 10-Feb-18 18 0 11-Feb-18 16 0 12-Feb-18 14 0 13-Feb-18 10 0 14-Feb-18 11 0 15-Feb-18 17 0 16-Feb-18 19 0 17-Feb-18 10 0 18-Feb-18 12 0 19-Feb-18 18 0 20-Feb-18 16 0 21-Feb-18 15 0 22-Feb-18 18 0 23-Feb-18 15 0 24-Feb-18 10 0 25-Feb-18 15 0 26-Feb-18 16 0 27-Feb-18 14 0 28-Feb-18 19 1 1-Mar-18 17 0 2-Mar-18 18 1

Thanks

Andre

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Flagging Latest End Date Available each month to 1

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)

Thanks

Raj

2 REPLIES 2
Super Contributor

## Re: Flagging Latest End Date Available each month to 1

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)

Thanks

Raj

Regular Visitor

## Re: Flagging Latest End Date Available each month to 1

Perfect ! Thanks a lot Raj

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 42 members 994 guests
Recent signins: