Powerbi DAX to calculate value based on two Datasets and next_month and previous month values

[scoder]

I have two datasets, DS1 which has Monthly Deduction which applies monthly, and DS2 related to subscription deductions happen based on the duration of Subcription_DS2

Below is Subcription_DS2 

User_ID	From_date	to_date	Updated_to_date	Amount
1	10-06-2023	10-03-2024		100
1	11-08-2023	11-05-2024	10-03-2024	200
2	13-12-2023	12-02-2024		150
2	14-01-2024	13-04-2024		300

Below is Monthly_DS1 

User_ID	Month_Date	Amount
1	31-04-2024	150
1	30-03-2024	20
2	31-04-2024	140
2	30-03-2024	50
1	29-02-2024	250
2	29-02-2024	90

I have to build some DAX which is Monthly_eff = (current_month_subcrption+monthly) - Next_month_subcrption
Measure based on Subcription_DS2
current_month_subcrption:- sum of Amount per userId where  report_month >= From_date and  report_month <= to_date and if Update_to_date is not null and report_month <= Update_to_date

Next_month_subcrption :- sum of Amount per userId where report_month+1 >= From_date and  report_month+1 <= to_date and if Update_to_date is not null and report_month+1 <= Update_to_date

report_month is the month selected by the user in the slicer

Monthly_DS1

monthly:- sum of Amount per userId where report_month = Month_Date

Finaly Monthly_eff = (current_month_subcrption+monthly) - Next_month_subcrption

Based on the sample data provided, 

Case1: 

For example, if the reporting month is Feb 2024 then for user_id 1

current_month_subcrption = 100+200,  monthly =250 , Next_month_subcrption = 100+200 

so Monthly_eff for Feb 2024 = (100+200+250) - (100+200) 

Case2: 

For example, if the reporting month is March 2024 then for user_id 1

current_month_subcrption = 100+200,  monthly =250 , Next_month_subcrption = 100, here 200 wont be considered as  report_month +1 is greater than update_to_date 

so Monthly_eff for Feb 2024 = (100+200+250) - (100+200) 

How to build the DAX for this? Also, there are no one-to-Many relations between these two datasets, it is many-to-many, so I tried to create DIM_DATE but I can have one-to-many with Monthly_DS1 not with Subcription_DS2.  Please guide how to build these measures 

[xifeng_L · Accepted solution]

Hi @scoder ,

The results of your second example don't seem quite right. Anyway, you can try the following measure:

Can refer to below pbix file.

Demo - Powerbi DAX to calculate value based on two Datasets and next_month and previous month values...

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[v-xuxinyi-msft · Accepted solution]

Hi @scoder 

Thanks for the reply from @xifeng_L , please allow me to provide another insight:

@scoder , the following steps are for your reference:

Slicer Table:

Create an intermediate table to establish the relationship

User_ID = VALUES(Subcription_DS2[User_ID])

Create several measures as follow

current_month_subcrption = 
    CALCULATE (
        SUM ( Subcription_DS2[Amount] ),
        FILTER (
            ALLEXCEPT ( Subcription_DS2, Subcription_DS2[User_ID] ),
            SELECTEDVALUE ( report_month[report_month] ) >= [From_date]
                && SELECTEDVALUE ( report_month[report_month] ) <= [to_date]
                || SELECTEDVALUE ( report_month[report_month] ) >= [From_date]
                    && SELECTEDVALUE ( report_month[report_month] ) <= [to_date]
                    && [Updated_to_date] <> BLANK ()
                    && SELECTEDVALUE ( report_month[report_month] ) <= [Updated_to_date]
        )
    )

monthly = 
CALCULATE (
    SUM ( Monthly_DS1[Amount] ),
    FILTER (
        ALLEXCEPT ( Monthly_DS1, Monthly_DS1[User_ID] ),
        YEAR ( [Month_Date] ) = YEAR ( SELECTEDVALUE ( report_month[report_month] ) )
            && MONTH ( [Month_Date] ) = MONTH ( SELECTEDVALUE ( report_month[report_month] ) )
    )
)

next_month_subcrption = 
VAR _next = DATE(YEAR(SELECTEDVALUE(report_month[report_month])), MONTH(SELECTEDVALUE(report_month[report_month])) + 1, DAY(SELECTEDVALUE(report_month[report_month])))
VAR _sum = 
    CALCULATE (
        SUM ( Subcription_DS2[Amount] ),
        FILTER (
            ALLEXCEPT ( Subcription_DS2, Subcription_DS2[User_ID] ),
            _next >= [From_date]
                && _next <= [to_date]
                || _next >= [From_date]
                    && _next <= [to_date]
                    && [Updated_to_date] <> BLANK ()
                    && _next <= [Updated_to_date]
        )
    )
RETURN
_sum

Monthly_eff = [current_month_subcrption] + [monthly] - [next_month_subcrption]

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xifeng_L · Accepted solution]

Hi @scoder ,

The results of your second example don't seem quite right. Anyway, you can try the following measure:

Can refer to below pbix file.

Demo - Powerbi DAX to calculate value based on two Datasets and next_month and previous month values...

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~