topic Re: Dynamic Project Cost in DAX Commands and Tips

Dynamic Project Cost

Anonymous — Sat, 05 Feb 2022 04:05:19 GMT

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

Re: Dynamic Project Cost

serpiva64 — Sat, 05 Feb 2022 08:02:15 GMT

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

Re: Dynamic Project Cost

Anonymous — Sun, 06 Feb 2022 04:03:20 GMT

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

Re: Dynamic Project Cost

v-chenwuz-msft — Wed, 09 Feb 2022 04:43:59 GMT

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.

Re: Dynamic Project Cost

Anonymous — Thu, 10 Feb 2022 10:45:24 GMT

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.

Re: Dynamic Project Cost

v-chenwuz-msft — Fri, 11 Feb 2022 02:02:57 GMT

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.