cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Pull Last Day of Month Value

I have the following table showing percentage change versus current and previous value:

 

img_pbi_change.GIF

Is there a way to group this monthly showing the database size for the last day of current month versus last day of the previous month and so on?

 

Thanks,

Frank

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Pull Last Day of Month Value

@anelliaf 
Not sure what output do you want, maybe provide a picture to show the expected result.

However, to get change of the last day of current month versus last day of the previous month. Hope this measure helps you.

 

Measure = 
Var CurrentMonthLastday = CALCULATE(SUM('Table'[database size]),ENDOFMONTH('Table'[Date]))
Var PreviousMonthLastday = CALCULATE(SUM('Table'[database size]),ENDOFMONTH(PREVIOUSMONTH('Table'[Date])))

Return (CurrentMonthLastday-PreviousMonthLastday)/PreviousMonthLastday

 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Pull Last Day of Month Value

@anelliaf 
Not sure what output do you want, maybe provide a picture to show the expected result.

However, to get change of the last day of current month versus last day of the previous month. Hope this measure helps you.

 

Measure = 
Var CurrentMonthLastday = CALCULATE(SUM('Table'[database size]),ENDOFMONTH('Table'[Date]))
Var PreviousMonthLastday = CALCULATE(SUM('Table'[database size]),ENDOFMONTH(PREVIOUSMONTH('Table'[Date])))

Return (CurrentMonthLastday-PreviousMonthLastday)/PreviousMonthLastday

 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper II
Helper II

Re: Pull Last Day of Month Value

The table i posted shows a few rows with dates and database size.  The database size for the last day of the month is what I need, not a SUM.

 

Thanks in advance for your help!

Highlighted
Microsoft
Microsoft

Re: Pull Last Day of Month Value

@anelliaf 


This sum in measure doesn't mean add up the values, it does not do calculations (min or max would get the same). 
You may try this measure, it should return the specific value of the last day of the month only. 


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors