Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic Project Cost

Hi,

 

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

Below is the sample data.

 

Project IDProject NameStart DateEnd DateMonthly Cost
1A01-01-1931-12-26200000

 

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

     

5 Replies

  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi serpiva64 ,

     

    Below is the sample data.

     

    Project IDProject Name Start Date End DateMonthly Cost
    1A01-01-1931-12-26200000
    2B01-06-2105-12-23100000
    3C15-04-1818-10-2275000

     

    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 
    A400000(200000*2)
    B100000(100000*1)
    C150000(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's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.