Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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. 

 

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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. 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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? 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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. 

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  thanks for this and I understand the logic, yet I don't get the number I'm looking for. I had expected to see for example for May 31 2019 a value of 21798. I do see this back in your graphs, but when I apply the same measure which you suggest, I still get the value of the sum of all values over the month in question.  I cannot find what I've done differently than your solution. 

 

Additional question I have is: if I were to have several type of 'parameters' in that field, so not just the one listed in my example, how would I write the filter to show only one? I tried applying filter(table, table[parameter]="selected paratemer" to the function, but am getting error message. 

 

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

 

thanks for your patience and ideally additional help

 

I will look at this today. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  hi there, 

 

can you please look into this once again/ more? Below is the measure I've applied and I'm still getting the sum over all days in the month, rather than only the sum of the value of the last day of the month. 

 

also, can you please advise how I would apply a filter? My table contains different categories in the same column and I'd need to be able to select on a specific on for my visuals. 

 

thanks again. 

 

PalletsInHighbay =
VAR
__EOMDATE=
EOMONTH(
max(T_Dynaman_KPI[Date]),
0
)
RETURN
if(Max(T_Dynaman_KPI[Date])==__EOMDATE,
sum(T_Dynaman_KPI[Value])
)
Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.