Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Uniform split and allocation amount into months

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Anonymous 

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.

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

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.