cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdegroot Frequent Visitor
Frequent Visitor

Last day of the month in Direct Query mode

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Last day of the month in Direct Query mode

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] )
)

7.png

 

 

Best Regards

Maggie

3 REPLIES 3
Community Support Team
Community Support Team

Re: Last day of the month in Direct Query mode

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] ) )
)
13.png
 
Best Regards
Maggie
sdegroot Frequent Visitor
Frequent Visitor

Re: Last day of the month in Direct Query mode

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. 

 

 

 

DATENUM_DOGS
4/1/20131
4/2/20132
4/3/20130
4/4/20132
4/5/20134
4/6/20135
4/7/20130
4/8/20132
4/9/20134
4/10/20135
4/11/20130
4/12/20130
4/13/20138
4/14/20139
4/15/20137
4/16/20135
4/17/20131
4/18/20134
4/19/20130
4/20/20134
4/21/20137
4/22/20134
4/23/20132
4/24/20130
4/25/20130
4/26/20132
4/27/20134
4/28/20135
4/29/20135
4/30/20136

 

Thank you!!

Community Support Team
Community Support Team

Re: Last day of the month in Direct Query mode

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] )
)

7.png

 

 

Best Regards

Maggie

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 118 members 1,693 guests
Please welcome our newest community members: