Uniform split and allocation amount into months

[xmarkx]

Hello there BI community!

I have a table which holds data on transactions for licences. The licences have a period they are valid, and a price for the whole period. I also have a date table which has Date, Year, Month, and Month-Year ("mmm-yyyy") columns among others.

The table looks as follows:
Example data table

The desired result would take PricePerPeriod and add it to the respective Month-Year.

• If the period is a year (PaymentMethod=3), then PricePerPeriod should be added to 12 months from the PeriodFrom month,
• If the period is one month (PaymentMethod=2), then PricePerPeriod should be added to the respective PeriodFrom month.

The PricePerPeriod colum is calcuated from the Price column based on the logic bescribed above.

The desired output would look like this:
Desired output

My closest solution looks like this, but it has unwanted results.

Total Value Year Month 3 = 
-- Similar code as Total Value Year Month 2, but without the DIVIDE function. The result is not really as desired
SUMX(
    VALUES('Date'[Month-Year]),
    CALCULATE(  
        SUMX(
            FILTER(
                Activations, 
                Activations[PeriodFrom] <= MAX('Date'[Date]) && Activations[PeriodTo] >=MAX('Date'[Date])
                ), 
                Activations[PricePerPeriod]
            )
        )
    )

I see (when I click on Name=A or Name=H in the Original input table) that it does not calculate the value 1000 there.I see (when I click on Name=F in the Original input table) that it does calculate the value 1000 for both January and February although I would expect it to only appear in January month (Note the leap day on February 29th).

I can upload a pbix file with my experiments, measures with comments, explanation of the problem, raw data and desired output if needed, but unfortunately i am not able upload it here on the forum directly.

If you have any questions or suggestions please feel free to ask.

Thank you all in advance! 🙂

[v-xiandat-msft]

Hi @xmarkx ,

Here’s how you can approach this:

• Create a calculated table called
• In the formula, use DAX to calculate the appropriate for each month based on the payment method.
• Sum up the for each month.

MonthlyLicenseValues =
ADDCOLUMNS (
    'Date',
    "PricePerMonth",
    SUMX (
        FILTER (
            Activations,
            Activations[PeriodFrom] <= 'Date'[Date]
                && Activations[PeriodTo] >= 'Date'[Date]
        ),
        IF (
            Activations[PaymentMethod] = 3, -- Yearly payment
            Activations[PricePerPeriod] / 12, -- Divide yearly price by 12 months
            Activations[PricePerPeriod]
        )
    )
)

Best Regards,

Xianda Tang

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

[xmarkx]

@v-xiandat-msft 

Thank you for your suggestion.

I tried your solution but the result were way off from the expected result. See the result and desired result comparison here:
Solution result VS desired result

Did you try it yourself?
Do you have any idea about an improvement?
Also i was looking for a solution as a measure so i can dinamically filter it.

[v-xiandat-msft · Accepted solution]

Hi @xmarkx ,

Below is my table1:

Below is my date:

The following DAX might work for you:

MonthlyLicenseValues = 
ADDCOLUMNS (
    'Date',
    "PricePerMonth1",
    SUMX (
        FILTER (
            Activations,
            Activations[PeriodFrom] <= 'Date'[Date]
                && Activations[PeriodTo] >= 'Date'[Date]
        ),
        IF (
            Activations[PaymentMethod] = 3, -- Yearly payment
            Activations[PricePerPeriod], -- Divide yearly price by 12 months
            Activations[PricePerPeriod]
        )
    )
)

The final output is shown in the following figure:

If that still doesn't fix it, please provide the pbix file.

Best Regards,

Xianda Tang

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