PowerBI DAX

[GauravKasar]

I have a user monthly subscription data in Power BI as below:

user_id	city	subscription_date	subscription_plan	plan_change_date	new_subscription_plan
UID0MASMOV	Delhi	22-Jan-24	Premium
UID0PTEFYK	Delhi	26-Jan-24	Basic	26-Mar-24	Premium
UID0JMJJUI	Delhi	3-Feb-24	Basic	3-May-24	Premium
UID0MYWGIX	Delhi	4-Feb-24	Basic	4-Jul-24	Premium
UID0DFPSBV	Pune	27-Feb-24	Premium
UID0AGZSTV	Mumbai	29-Feb-24	Basic
UID0MTOBFR	Mumbai	7-Apr-24	Premium
UID0LINGDZ	Delhi	12-Apr-24	Premium	12-Jun-24	Basic
UID0LOWWKR	Pune	10-May-24	Basic	10-Aug-24	Premium
UID0BAGHRJ	Delhi	27-May-24	Basic

If the plan change date is null that means the user is continuing with the same plan. Plan prices are basic @ 100$, premium @ 200$.

I am not able to calculate a meaure in Power BI that returns the total revenue dynamically for the selected month from slicer....the measure that can be used either as card visual or in a table visual that calculates the total revenue for the selected month.

I am stuck at this since last month.. This is a sample data I created for asking the question. Actual data is 50k rows with 13 columns. Someone please help on solving this.

[v-kathullac]

Hi @GauravKasar  ,

we wanted to kindly follow up to check if the solution provided by the superuser for your issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[ArwaAldoud]

Hi @GauravKasar 
to calculate Total Revenue dynamically based on the selected month, first create a Date Table and link it to SubscriptionData[subscription_date]. Then, use this measure:

update Total Revenue =

VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month Number])  

RETURN  

SUMX(  

    FILTER(SubscriptionData,  

        MONTH(SubscriptionData[subscription_date]) = SelectedMonth ||  

        (NOT(ISBLANK(SubscriptionData[plan_change_date])) && MONTH(SubscriptionData[plan_change_date]) = SelectedMonth)  

    ),  

    IF(

        NOT(ISBLANK(SubscriptionData[plan_change_date])),

        IF(SubscriptionData[new_subscription_plan] = "Basic", 100, 200),

        IF(SubscriptionData[subscription_plan] = "Basic", 100, 200)

    )  

)  

 

I have attached the PowerBIDAX.pbix file for reference.

If this response was helpful, please accept it as a solution and give kudos to support other community members.

[GauravKasar]

Hello @ArwaAldoud,

I really appreciate your efforts for providing the solution.

But in your solution, it is not showing the revenue for the months for which there are no new subscribers (e.g., March-2024, where the revenue is $900 from the past month subscribers).

 Also the expected solutions (that I calculated manually) are not matching.

Below are the expected outputs:

Month	individual revenue breakup	Revenue (Excpected solution)
Jan-24	200+100	300
Feb-24	100+200+100+100+100+200	800
Mar-24	100+200+100+100+200+200	900
Apr-24	200+200+100+200+100+100+200+200	1300
May-24	100+100+200+200+100+200+100+200+200+200	1600
so on…

Please check.

Thanks,

Gaurav Kasar

[ArwaAldoud]

Hi @GauravKasar 

Since the relationship in the DateTable is based on subscription_date, selecting a month from the DateTable filters SubscriptionData only by subscription_date, ignoring plan_change_date.

There are multiple ways to solve this issue, but I chose to create calculated columns instead of a measure. This approach helps with debugging and ensures the logic is correctly applied to each row.

I created the following calculated columns: Effective_Month, Effective_Plan and Effective_Year. Then, I calculated revenue based on these columns.

Additionally, I created two measures:
Revenue per Selected Month

Cumulative Revenue (as per your recent request with the expected results)

You can find the attached .pbix file for reference.

If this response was helpful, please accept it as a solution and give kudos to help other community members.

[v-kathullac]

Hi @GauravKasar  ,

we wanted to kindly follow up to check if the solution provided by the superuser for your issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[GauravKasar]

Hello @ArwaAldoud,

I really appreciate your efforts for providing the solution.

But in your solution, it is not showing the revenue for the months for which there are no new subscribers (e.g., March-2024, where the revenue is $900 from the past month subscribers).

 Also the expected solutions (that I calculated manually) are not matching.

Below are the expected outputs:

Month	individual revenue breakup	Revenue (Excpected solution)
Jan-24	200+100	300
Feb-24	100+200+100+100+100+200	800
Mar-24	100+200+100+100+200+200	900
Apr-24	200+200+100+200+100+100+200+200	1300
May-24	100+100+200+200+100+200+100+200+200+200	1600
so on…

Please check.

Thanks,

Gaurav Kasar

[Ashish_Mathur]

Hi,

In a simple table visual, show the expected result for 2 months very clearly.

[GauravKasar]

Hello @Ashish_Mathur,

Below are the expected results for the selected month:

Month	Revenue
Jan-24	300
Feb-24	800
Mar-24	900
Apr-24	1300
May-24	1600
so on…

Thanks,

Gaurav Kasar

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[DataNinja777]

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,

[GauravKasar]

@DataNinja777 , can you please share the pbix file. I am not getting correct data modelling too.

Below are the expected results (I calculated it manually):

Month	individual revenue breakup	Revenue (Excpected solution)
Jan-24	200+100	300
Feb-24	100+200+100+100+100+200	800
Mar-24	100+200+100+100+200+200	900
Apr-24	200+200+100+200+100+100+200+200	1300
May-24	100+100+200+200+100+200+100+200+200+200	1600
so on…

Please check.

Thanks,

Gaurav Kasar