Filter to last day of month or max day of table

[BIBRO]

I am trying to get the distinctcount of id's where the date = last day of the month or current day. The formula looks like this.

Calculate(DISTINCTCOUNT(TABLE[ID],FILTER(TABLE,TABLE[DATE] IN {VALUES(DATE_TBL[LAST_DAY_OF_MONTH])} || TABLE[DATE] = MAX(TABLE[DATE])

I get the expected results when it's just Last day of Month. When I include || Max(Table[Date}) I don't get the results that I want.

[v-jianboli-msft · Accepted solution]

Hi @BIBRO ,

Based on your description, I have created a simple sample;

Please try:

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Date]=MAX('Table'[Date])||[Date]=EOMONTH([Date],0)))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@BIBRO ,

Sumx(Values(Table[Month Year]) , calculate(lastnonblankvalues( Table[Date], DISTINCTCOUNT(TABLE[ID]) ) ) )