PowerBI DAX

Hi GauravKasar ,

To calculate the total revenue dynamically based on the selected month in Power BI, we need a DAX measure that determines which subscription plan each user had during the selected month. The logic must handle both users who never changed their plan and those who switched from Basic to Premium or vice versa. We'll create a measure that checks whether the selected month falls before or after the plan_change_date and then assigns the correct price—$100 for Basic and $200 for Premium.

Here's the DAX measure you can use:

Monthly Revenue = 
VAR SelectedMonth = SELECTEDVALUE('DateTable'[Date])
VAR SelectedMonthStart = DATE(YEAR(SelectedMonth), MONTH(SelectedMonth), 1)
VAR SelectedMonthEnd = EOMONTH(SelectedMonthStart, 0)

RETURN
SUMX (
    'Subscriptions',
    VAR StartDate = 'Subscriptions'[subscription_date]
    VAR PlanChangeDate = 'Subscriptions'[plan_change_date]
    VAR InitialPlan = 'Subscriptions'[subscription_plan]
    VAR NewPlan = 'Subscriptions'[new_subscription_plan]

    VAR IsInInitialPlan = StartDate <= SelectedMonthEnd && (ISBLANK(PlanChangeDate) || PlanChangeDate > SelectedMonthEnd)
    VAR IsInNewPlan = NOT ISBLANK(PlanChangeDate) && PlanChangeDate <= SelectedMonthEnd

    VAR Revenue = 
        SWITCH(
            TRUE(),
            IsInInitialPlan && InitialPlan = "Basic", 100,
            IsInInitialPlan && InitialPlan = "Premium", 200,
            IsInNewPlan && NewPlan = "Basic", 100,
            IsInNewPlan && NewPlan = "Premium", 200,
            0
        )
    RETURN Revenue
)

This measure assumes you have a proper DateTable set up in your data model, and that your slicer is based on a single selected date representing the target month (like the first day of the month). The calculation checks if the user was still on their original plan in the selected month or if they had switched to a new one and returns the appropriate amount. The SUMX function iterates over each row of the subscriptions table and adds up the calculated revenue per user. This measure can be used in a card visual to show total revenue for the selected month, or in a table visual broken down by city, user, or other attributes.

Best regards,