Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
scoder
Frequent Visitor

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

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_IDFrom_dateto_dateUpdated_to_dateAmount 
110-06-202310-03-2024 100 
111-08-202311-05-202410-03-2024200 
213-12-202312-02-2024 150 
214-01-202413-04-2024 300 
      

 

Below is Monthly_DS1 

User_IDMonth_DateAmount
131-04-2024150
130-03-202420
231-04-2024140
230-03-202450
129-02-2024250
229-02-202490

 

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 

 

 

2 ACCEPTED SOLUTIONS
xifeng_L
Super User
Super User

Hi @scoder ,

 

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

 

xifeng_L_0-1715526014576.png

 

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~

View solution in original post

Anonymous
Not applicable

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:

vxuxinyimsft_2-1715590567751.png

 

Create an intermediate table to establish the relationship

User_ID = VALUES(Subcription_DS2[User_ID])

 

vxuxinyimsft_0-1715590462272.png

 

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:

vxuxinyimsft_1-1715590522286.png

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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:

vxuxinyimsft_2-1715590567751.png

 

Create an intermediate table to establish the relationship

User_ID = VALUES(Subcription_DS2[User_ID])

 

vxuxinyimsft_0-1715590462272.png

 

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:

vxuxinyimsft_1-1715590522286.png

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
Super User
Super User

Hi @scoder ,

 

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

 

xifeng_L_0-1715526014576.png

 

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~

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.