End of Week Count Hierarchy

[calgarygeologis]

I have a count measure which I'm trying to display in a table with the week number and with the Month name. Some weeks span across the end of a month and my table is return two weekly values (one at the end of the month and one at the end of the actual week.) Can I get the table to display only the end of week value?

As you can see week 14 starts at the end of March and it ends in April. I am getting a value for the last day of March in week 14 and for the last day of week 14. I would like to only see the last day of week 14.

In case it helps here is my count measure:

Count =

SUMX(

values ( Location[Area]),

VAR LastCountDate = Calculate( MAX ( FactTable[Date] ))

RETURN

CALCULATE (

SUM ( FactTable[Data] ),

'DateTable'[Date] = LastWorkforceDate

))

[amitchandak · Accepted solution]

@calgarygeologis , You need to have column like these in date table and then you can filter on last date

Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

last date = if([Week End date] <eomonth([date],0),[Week End date],eomonth([date],0))

Flag =if([Date] =[last date],1,0)