cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

publish only the value of the last or the first date of a month

Hi there,

I have a table with entries being added each day. For reporting purposes, I need to show a trend, taking the sum of the values entered on either only the first day of each month or the last day of each month. The dates are in date format, the values in numbers and then there are categories of properties I need to report on. simple table, yet I can't figure out how to write my measure to report the first / last date totals for each month. 

 

thanks!

13 REPLIES 13
Super User
Super User

Re: publish only the value of the last or the first date of a month

@Anonymous do you want to show the sum of values for whole month or just the data entered on last or first day of the month.

 

For time intelligence, it is best practice to have date dimension in your table and it becomes easy to work with these kind of requriements. There are many posts/blogs on how to add date/calendar dimension in your model. 

 

This is the first starting point to achieve result






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Re: publish only the value of the last or the first date of a month

@parry2k 

thanks for the very quick response. I do have a date dimension in my table. summing the total for the entire month I know how to do. What I'm looking for is the sum of only the last day or in some other cases first day of each month. I do know how to find and report on the last day/ first day of one specific month, but not how to calculate such for a each month over a year for example. 

 

 

Super User
Super User

Re: publish only the value of the last or the first date of a month

@Anonymous that should be easy but what is the logic for when to get last and when to first day of the month?






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Re: publish only the value of the last or the first date of a month

@parry2k  great that this should be easy. Whether I'd need first or last day of a month is not related. for some groups/ metrics I need to publish a value based on the first day of the month, for others the last and for yet others the sum of an entire month. None of those are related however or dependent on each other. 

Super User
Super User

Re: publish only the value of the last or the first date of a month

@Anonymous can you share sample pbix and I will work on it.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Re: publish only the value of the last or the first date of a month

@parry2k  - appreciate the offer to help --  how do I share a file though through this messages. As far as I know I can't attach files no? 

Super User
Super User

Re: publish only the value of the last or the first date of a month

@Anonymous you can share the file thru one drive/google drive. 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Re: publish only the value of the last or the first date of a month

@parry2k 

 

https://lantmannen-my.sharepoint.com/:u:/p/michiel_van_der_veer/EVwgyi-3dJ5KgAHjoFX3qGMBWq8zULUv6stO...

 

does this work?  As you'll see there are entries each day of the month. I want to only see the value of each last day of the month. 

Super User
Super User

Re: publish only the value of the last or the first date of a month

@Anonymous Add this measure 

 

EOM Total = 
VAR __eomDate = 
    EOMONTH( 
        MAX( Table_Query_from_SR05122[Date] ), 
        0 
    )
RETURN
IF ( MAX( Table_Query_from_SR05122[Date] ) == __eomDate,
    SUM( Table_Query_from_SR05122[Value] ) 
)

And this is what you get when you use this in the visual

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 155 members 1,639 guests
Please welcome our newest community members: