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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MishelHoff
Regular Visitor

How to calculate total to date per row of a table?

Hi,

 

Please can you help me calculate the total to date per row of a table? My data looks like this:

 

  • Activity ID is the activity that generates cost.
  • Date is the date at which that cost was generated. For Dates that are before the date in Data Date the cost has been realised. For Dates that are after Data Date the cost is a forecast.
  • Actual value is the cost for that activity which occured at the date in Date
  • Date date is the date the report was generated.

 

I want to know, for each Activity ID, what is the total sum of Actual Value for each instance where Date is before Data Date

 

MishelHoff_1-1700217533071.png

 

I have created the following measure which is not working:

Actuals to Date = CALCULATE(
    SUM('Actuals Data'[Actual Value]),
    FILTER(
        ALL('Actuals Data'), 'Actuals Data'[Date] <= MAX('Actuals Data'[Data Date])))
 
Thank you!

 

 

 

2 REPLIES 2
MishelHoff
Regular Visitor

Hi @Ritaf1983 

 

Thank you so much for responding to me. I think I have solved this particular issued but I really apppreciate you taking the time to respond and for elaborating on how I could make my post more useful. I'm sure I will continue to need help in the future so I will make sure to include the details you suggested when I next post a questions. Many thanks

Ritaf1983
Super User
Super User

Hi @MishelHoff 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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