Last day of the month in Direct Query mode

[sdegroot]

Hello,

I'm using direct query mode and I'm trying to filter monthly values based on the last day of each month. All the postings I've seen have a solution involving an additional comlumn being added within "Edit Queries", unfortunately this is not possible in direct query mode and the ENDOFMONTH expression does not work. 

Any suggestions? 

I have a column called "Day_of_Week_sort" which could be used to create a measure for Maximum value which I think may help...

Thank you! 

[v-juanli-msft]

Hi @sdegroot

Would you like calculate the last day of each month based on a date column? 

I test it in in Direct Query mode and figure out with a workaround to get the max day of each month.

It turns out the same result as ENDOFMONTH expression.

new column:

day = DAY([FullDateAlternateKey])

year/month =
CONCATENATE (
    CONCATENATE ( YEAR ( [FullDateAlternateKey] ), "/" ),
    MONTH ( [FullDateAlternateKey] )
)

new measure:

end day of each month =
CALCULATE (
    MAX ( [FullDateAlternateKey] ),
    FILTER ( ALL ( DimDate ), [year/month] = MAX ( [year/month] ) )
)

Best Regards

Maggie

[sdegroot]

That's great Maggie! For some reason I still wasn't allowed to do the second step in Direct Query mode so I just did this:

End of Month = MAX(TABLE[Day]) which worked just fine!

I am now having difficulty linking this to the values in the  column I'm actually trying to filter. Here is an example of what I'm trying to apply this measure to. I want to know the number of dogs in the park on the last day of month. For April, there were 6 dogs in the park on the last day. 

I'm not sure how to apply my new measure End of Month = MAX(LU_DATE_v[Day Only]) to this. 

DATE	NUM_DOGS
4/1/2013	1
4/2/2013	2
4/3/2013	0
4/4/2013	2
4/5/2013	4
4/6/2013	5
4/7/2013	0
4/8/2013	2
4/9/2013	4
4/10/2013	5
4/11/2013	0
4/12/2013	0
4/13/2013	8
4/14/2013	9
4/15/2013	7
4/16/2013	5
4/17/2013	1
4/18/2013	4
4/19/2013	0
4/20/2013	4
4/21/2013	7
4/22/2013	4
4/23/2013	2
4/24/2013	0
4/25/2013	0
4/26/2013	2
4/27/2013	4
4/28/2013	5
4/29/2013	5
4/30/2013	6

Thank you!!

[v-juanli-msft · Accepted solution]

Hi @sdegroot

With this meaure, I can get the number of dogs in the park on the last day of month. ([Measure]->End of Month)

Measure 2 =
CALCULATE (
    MAX ( [num of dog] ),
    FILTER ( ALLEXCEPT ( Sheet1, Sheet1[year/month] ), [date] = [Measure] )
)

Best Regards

Maggie