Forum Discussion

AliceW's avatar
AliceW
Power Participant
6 years ago
Solved

Divide a quantity between given months

Hello everyone

I'm not sure how to proceed, and I could use some help.

I have these fields:

  • Amount (say, 1,200 Euros)
  • Start date (say, 01-January-2020)
  • End date (say, 31 say-2020).

In the source table, there is one line for each product:

Up IDProductStart dateEnd dateAmount
1Main product01-January-202031-12-20201200

I need to have the amount equally divided between the months of the start and end date.

The goal would be a table like this:

Up ID + ProductoIncome Month-YearAmount of income
1 - Main productJanuary-2020100
1 - Main productFebruary-2020100
1 - Main product...
1 - Main productDec-2020100

The dates would be in different years, so building a column for each month could be daunting.

The key would be the only Opp ID + Product...

Thanks a lot

Alice

  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    AliceW ,

     

    You can creae a measure using dax below:

    Result =
CALCULATE (
    SUM ( Table[Amount] ),
    ALLEXCEPT ( Table, Table[Up ID], Table[Product] )
)
    / DATEDIFF ( Table[Start date], Table[End date], MONTH )

     

    Community Support Team _ Jimmy Tao

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

     

2 Replies