Help with Contract burn - mutiple contracts with start and end dates

[Anonymous]

I have a project where I have to calculate the burn rate based on labor ceiling for contracts per month based on the fee charged that month.

I have set up the calendar and labor ceiling tables and getting the payment date and amount charged. I am able to calulate the running totals but have an issue from when it starts counting down the contract since it is pulling data for prior contracts.

How do I make sure to only calculate down from the start date (month and year) for the projects. It is also summing the prior project ceilings together.

[v-xicai]

Hi @Anonymous ,

You may create measure like DAX below .

Cumulative total Per Year =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        'labor ceiling',
        'labor ceiling'[contracts] = MAX ( 'labor ceiling'[contracts] )
            && YEAR ( 'labor ceiling'[Date] ) = YEAR ( MAX ( 'labor ceiling'[Date] ) )
            && MONTH ( 'labor ceiling'[Date] ) <= MONTH ( MAX ( 'labor ceiling'[Date] ) )
    )
)

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

 @v-xicai Thank you so much Amy! I am trying to figure out how to attach the file. I don't see an option to just upload any file.

[v-xicai]

Hi @Anonymous ,

You can also upload sample pbix to OneDrive (you can find it in following location) and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

@v-xicai were you able to get to my file on onedrive? Posted again just in case.

https://1drv.ms/u/s!AmJqEyBIpAulgixt_wHZNaxL4Ofm 

[Anonymous]

@v-xicai  Here is the link for onedrive: https://1drv.ms/u/s!AmJqEyBIpAulgixt_wHZNaxL4Ofm 

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

@amitchandak I am new to Power BI and not sure your post helps in my case since it is only calculating the number of employees in a previous and given month. It would be more relavant with total salary for the year and then amount paid each month and counting down to calculate the burn report.