Calculate Sum of Values Based on Date and Product Type for filtering

[Palan22]

Hello,

I need some guidance, I currently have a data table visual that is pulling the following information where the ‘Change’ calculated column = Sum of the current 'Received' value + any <= previous post date 'Received'values. Now, my issue here is that when I filter by ‘project type’ (using page filter), the ‘Change’ values are accounting for any previous post-date values regardless of the project type. How do I set it up to only calculate the Sum of all ‘Received’ units + any ‘Received’ units with the same project type that have a post-date that is =< than the current row’s post-date?

To give additional details about my setup, I have 3 data tables: ProjectID, Goals, and Services. The data table ProjectID has a relationship with both the goals and service tables based on the data [ProjectID] that is linking them all together. 

[Anonymous]

Hi @Palan22 

Could you provide me a sample without sensitive data by your Onedrive for Business?

And show me your calculate logic to me.

It is better to show me a screenshot of the result you want.

This will make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

[Palan22 · Accepted solution]

@Anonymous Hi! Thank you for reaching out to assist. I was able to figure it out by using the following:

Calculate(SUM(Services[Received]),Filter(All(Services[PostDate]),Services[PostDate] <= MAX(Services[PostDate])),Filter(All(Services[Status]),Services[Status] = "Completed"))

[parry2k]

@Palan22 Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490