Dynamic Project Cost

[Anonymous]

Hi,

I'm trying to get the Project cost dynamically based on Month and Year.

Below is the sample data.

Project ID	Project Name	Start Date	End Date	Monthly Cost
1	A	01-01-19	31-12-26	200000

I would like to get the Monthly and Yearly cost dynamically based on the date selected.

Any help in writing DAX query would be appreciable.

Thank you.

Regards,

Prajna

[Anonymous]

Hi @serpiva64 ,

Below is the sample data.

Project ID	Project Name	Start Date	End Date	Monthly Cost
1	A	01-01-19	31-12-26	200000
2	B	01-06-21	05-12-23	100000
3	C	15-04-18	18-10-22	75000

For an instance, if I select the date period from May 2021 to June 2021, I would like to see the below result based on the Start and End Date of the project dynamically.

Project Name	Cost
A	400000	(200000*2)
B	100000	(100000*1)
C	150000	(75000*2)

In order to achieve this, I should write a DAX measure as it wont work well with just visuals.

Thanks,

Prajna

[v-chenwuz-msft]

Hi @Anonymous ,

You can try this code:

Cost =
VAR _start =
    IF(
        MIN( 'Calendar'[Date] ) >= SELECTEDVALUE( 'Table'[Start Date] ),
        MIN( 'Calendar'[Date] ),
        SELECTEDVALUE( 'Table'[Start Date] )
    )
VAR _end =
    IF(
        MAX( 'Calendar'[Date] ) <= SELECTEDVALUE( 'Table'[End Date] ),
        MAX( 'Calendar'[Date] ),
        SELECTEDVALUE( 'Table'[End Date] )
    )
VAR _diff =
    IF( _start < _end, DATEDIFF( _start, _end, MONTH ) + 1, 0 )
RETURN
    _diff * SELECTEDVALUE( 'Table'[Monthly Cost] )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-chenyue-msft ,

Thanks for the measure.
Although this is what I wanted, the measure doesn't work fine with the columns from other tables.
I have connected the Project table to an Employee table based on Project ID to get the employee details.

I would like to see the Project name, respective Employee email and the cost.
Sample below.

Could you kindly suggest on how can the measure work when a column from other tables are got in the table.

Thanks,

Regards.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous 

Please change the relationship between fact table and employee table and set the cross filter direction is both.

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[serpiva64]

Hi, 

please provide a sample date with just more then 1 row.

To make what you want you don't need writing Power query or Dax, just create visuals