How to calculate cost (by year) across multiple years when the service dates starts mid year?

[Anonymous]

Hello all, hope everyone is staying safe during this crazy time.

I need some help with a problem. What i'm trying to do is this:

I have a Purchase order data where we have service dates. For example, cleaning services for Jan 1 2019 - Dec 31 2019. I can easily figure out the spend for the year 2019. However, sometimes I have Service dates where they span across multiple years and start mid year.

e.g. Mar 1 2019 - Mar 1 2022 for $5,000

How can I show what my projected spend would be for each year?

What I would do in excel is calculate cost per month ($5,000 / 36) then calculated how many months remaining in each year, then multiplied by the monthly cost. It would look similar something like this:

2019 $1,388.89 (10 months * 138.89)
2020 $1,666.67 (12 months * 138.89)
2021 $16,666.67 (12 months * 138.89)
2022 $277.78 (3 months * 138.89)

On second though, maybe its better to calculate cost by day since service start dates can start mid month as well?

Any help would be greatly appreciated. Thank yoU!

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You could also refer to this blog:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Regards,

Lin

[Greg_Deckler]

Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364