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

Getting the SUM of CurrentValue field but only for last month

Hi there,
I have been struggeling with this for quite some time now. Don't know what I am doing wrong.
I have the following FACT table which gets generated merging excel files together via a folder structure:

Capture2.PNG 
This will get updated once a month (in the end of each month). The actual stocks and Investments will not vary too much, but their value will. Therefor I need to be able to calculate the SUM of the CurrentValue colmn, but only for the latest recorded month. In this instance it is all the rows from 31-01-2021. Next month it will be 31-01-22, and so forth.

I have a DIM_Dates table as well connected to the FACT table:
Capture.PNG

 

I have tried multiple things but it doesn't seem to work for me. For example I tried these measures:

Total Portfolio Value = SUM(FACT_Investments[CurrentValue])

 

Current Portfolio Value =
CALCULATE(Calculations_Basic[Total Portfolio Value], DATEADD(DIM_Dates[Date], -1, MONTH))

But this does not work and still gives me the sum of ALL my rows (hence: both november and december 2021):
Capture3.PNG
The value should be 457.510,45 kr.

Thanks in advance.
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@CHFarver , Try a measure like

 

Current Portfolio Value =
var _max = eomonth(maxx(allselected('FACT_Investments'),'FACT_Investments'[date]),0)
return
CALCULATE(Calculations_Basic[Total Portfolio Value], filter(DIM_Dates, eomonth(DIM_Dates[Date],0) =_max ))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

View solution in original post

2 REPLIES 2
CHFarver
Frequent Visitor

This seems to work perfect so far. And I just tested when applying a new sheet as an example. Thanks alot!

amitchandak
Super User
Super User

@CHFarver , Try a measure like

 

Current Portfolio Value =
var _max = eomonth(maxx(allselected('FACT_Investments'),'FACT_Investments'[date]),0)
return
CALCULATE(Calculations_Basic[Total Portfolio Value], filter(DIM_Dates, eomonth(DIM_Dates[Date],0) =_max ))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!