topic Re: Powerbi DAX to calculate value based on two Datasets and next_month and previous month values in DAX Commands and Tips

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

scoder — Sat, 11 May 2024 17:55:11 GMT

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

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

xifeng_L — Sun, 12 May 2024 15:06:13 GMT

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.pbix

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

Thank you~

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

Anonymous — Mon, 13 May 2024 08:59:05 GMT

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.