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