Split an amount in a period

[andreazambon]

Hi there. 

Suppose to have some projects and a yearly budget for each progress to split in a month graph.

The target is to show periodically the cost of this project:

1) suppose to pay the cost of the project every month:

It's easy to split this amount in 12 months, as I did in the attached example.

2) suppose to pay the cost of the project every N months:

This is more challenging: for example, if I pay every 4 months for the Project A (total budget: 12.000$) I would like to see 3 columns with 4000$ in April, August, December. 

Some advices to reach this goal?

Here's the pbix file for testing:

https://we.tl/t-Gx3rfLQVkL

Thanks.

[v-yifanw-msft]

Hi @andreazambon ,

Depending on the information you have provided, you can follow the steps below:

1.Add new measure.

Cost Allocation =
VAR _every4month = 4
VAR _permonth = 12 / _every4month
VAR _month =
    SELECTEDVALUE ( DateTable[month] )
RETURN
    IF (
        _month = 4
            || _month = 8
            || _month = 12,
        SUM ( Projects[Budget] ) / _permonth,
        0
    )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[andreazambon]

HI @v-yifanw-msft , thanks for your answer. 

More probably, I would work with a "frequence" field like this:

So, in order to adapt your measure to this scenario, I tried to made this code: 

Allocazione costi = 

VAR _permonth = 12/average(Projects[Frequence])
VAR _month = SelectedValue(DateTable[month])
 RETURN
    SUM(Projects[Budget])/_permonth

but this is not adapted to calculate the average BY PROJECT, in order to give the right period for each project.

This is the result:

This is the target (thanks excel)