Running Total Cost Savings Measure depending on certain conditions with multiple tables.

[komo]

Hello, 

I am trying to create a measure that would calculate the money saved depending on certain conditions between various tables. 

I have a date table:

that shows date range from Nov 20,2022 to today. 

It has one column with a mm/dd/yyyy format.

I have a table regarding CostSavings(CostSavings)

ID	CostProject	Status	Created ProjectDate	Completed ProjectDate	CostAmount
1	A	In Progress	11/20/2022		$15
2	B	Completed	11/20/2022	1/2/2023	$10
3	B	Completed	12/1/2022	1/5/2023	$30
4	A	Completed	1/3/2023	2/8/2023	$10
5	A	Completed	1/5/2023	1/30/2023	$10

I have Project A table (ProjectA)

Project	Status	CompletedDate	ImpactedChanges
A	In Progress		5
A	Completed	1/2/2023	10
A	Scheuled		2
A	Completed	2/8/2023	10
A	Completed	2/15/2023	10

I have Project B table (ProjectB)

Project	Status	CompletedDate
B	In Progress
B	Completed	1/2/2023
B	Scheuled
B	Completed	2/8/2023
B	Completed	2/15/2023

For the measure, I need a few things to happen:

Based on the CostSavings, if the item is "Completed" to running total the "CostAmount" and multiply by project's running total of completed items.

For example, For item 2 in CostSavings completed in 1/2/2023 has $10. In ProjectB there are 3 "Completed" line item with a completed date equal or after the 1/2/2023 date. So $10*3 = $30.

But for item 3 in CostSavings completed in 1/5/2023, it has savings of $30. In ProjectB, we can only count 2 "Completed" since the first one started on 1/2/2023 which is before the CostSavings table took consideration of that item. So $30*2 =$60.

Together, so far, the costsavings for ProjectB with a "Completed" stautus on both sides will be $30+$60 = $90.

For Project A, we will need to consider the ProjectA calculating only the "Completd" status and the amount of "Impactedchanges. Item4 in CostSavings completed on 2/8/2023 of $10. And in the ProjectA table 2 items were also completed on or after the date (2/8/2023 - 10 impacted changes and 2/15/2023 - 10 impacted changes). Therefore $10 *20 (impacted changes) = $200 dollars. 

The date table has an inactive relationship of the date to the CostSavings table Created ProjectDate and Completed ProjectDate.

[rautaniket0077]

Hi @komo,
firstly change your date calumns to the proper format because for the below measures to work you need dates in Date data type
for this use below steps in power query

Steps to follow :

1. Split the Date column by "/" Delimiter

2. Change the data type of the newly created columns to Whole number

3. Bring the Day column first by dragging, Month column second and Year column last

4. Select all these three collumns and go to Add column Tab, Select Merge Columns and in Merge dialog, Select "/"Separator.

5. Change the data type of the merged column to Date.

then use the below 2 measures.

1) Measure =

var a = SELECTEDVALUE(CostSavings[Completed ProjectDate])

var b =

COUNTROWS(

    FILTER(

        ProjectB,

        ProjectB[CompletedDate] >= a)

)

var c = CALCULATE(b * MAX(CostSavings[CostAmount]))

return

SWITCH(

    TRUE(),

    MAX(CostSavings[CostProject]) = "B", c, 0

)

2) Measure 2 =

var a = SELECTEDVALUE(CostSavings[Completed ProjectDate])

var b =

SUMX(

    FILTER(

    ProjectA,

    ProjectA[CompletedDate] >= a

    ),

    ProjectA[ImpactedChanges])

var c = CALCULATE(b*MAX(CostSavings[CostAmount]),CostSavings[CostProject] = "A",CostSavings[Status] = "Completed")

return

IF(ISBLANK(c),0,c)

it will get you this.
please accept my answer as solution if it solves your issue.

[komo]

Hi @rautaniket0077 ,

Thank you very much for the assistance. I tried your measures and it appears to work halfway. On an individual line level, it seems to work as it should. But the total line does not seem to compute it properly:

Total should be $324.....but it is showing only $300. 

In addition, is there a reason why switch function was used for the first measure only and for the second measure's return doesn't have the switch?

[amitchandak]

@komo , Please refer if this approach can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU