Flagging Latest End Date Available each month to 1

[andrehawari]

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

[Anonymous · Accepted solution]

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)

Thanks

Raj

[andrehawari]

Perfect ! Thanks a lot Raj