Forum Discussion

GauravKasar's avatar
GauravKasar
Regular Visitor
1 year ago

PowerBI DAX

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 Prem...
Need Help
ArwaAldoud's avatar
ArwaAldoud
Super User
1 year ago

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's avatar
GauravKasar
Regular Visitor
1 year ago

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:

 

Monthindividual revenue breakupRevenue (Excpected solution)
Jan-24200+100300
Feb-24100+200+100+100+100+200800
Mar-24100+200+100+100+200+200900
Apr-24200+200+100+200+100+100+200+2001300
May-24100+100+200+200+100+200+100+200+200+2001600
so on…  

 

Please check.

 

Thanks,

Gaurav Kasar

  • ArwaAldoud's avatar
    ArwaAldoud
    Super User
    1 year ago

    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's avatar
      v-kathullac
      Community Support
      1 year ago

      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.