cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuliaWendel
Regular Visitor

Calculation within a Column

Hello,

 

I got another question

is it possible to calculate the difference within one column between two months?

 

E.g. 

Month: January

Product launch: 5

 

Month: February

Product launch: 7

 

--> calculate in new column the difference between "Product launch" February and "Product launch" January:

7-5 = +2

ExplanationExplanation

Maybe anybody know a solution.

Thank you in advance!

Julia

3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

Hi @JuliaWendel

 

Try this solution.

If you donot have a column for Month Numbers... add this calculated column

 

Month_Number =
SWITCH (
    Table1[Months],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12
)

Now you can get the desired difference column using below

 

Difference =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Product Launch] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Month Number]
                = EARLIER ( Table1[Month Number] ) - 1
                && Table1[Year] = EARLIER ( Table1[YEAR] )
        )
    )
VAR PreviousMonthValueforJan =
    CALCULATE (
        SUM ( Table1[Product Launch] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Month Number] = 12
                && Table1[Year]
                    = EARLIER ( Table1[YEAR] ) - 1
        )
    )
RETURN
    Table1[Product Launch]
        - IF ( Table1[Month_Number] = 1, PreviousMonthValueforJan, PreviousMonthValue )

 

Regards
Zubair

Please try my custom visuals


View solution in original post

v-ljerr-msft
Microsoft
Microsoft

Hi @JuliaWendel,

 

Have you tried the solution provided by @Zubair_Muhammad above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

 

If you still have any question on this issue, feel free to post here. Smiley Happy

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Microsoft
Microsoft

Hi @JuliaWendel,

 

Have you tried the solution provided by @Zubair_Muhammad above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

 

If you still have any question on this issue, feel free to post here. Smiley Happy

 

Regards

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

Hi @JuliaWendel

 

Try this solution.

If you donot have a column for Month Numbers... add this calculated column

 

Month_Number =
SWITCH (
    Table1[Months],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12
)

Now you can get the desired difference column using below

 

Difference =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Product Launch] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Month Number]
                = EARLIER ( Table1[Month Number] ) - 1
                && Table1[Year] = EARLIER ( Table1[YEAR] )
        )
    )
VAR PreviousMonthValueforJan =
    CALCULATE (
        SUM ( Table1[Product Launch] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Month Number] = 12
                && Table1[Year]
                    = EARLIER ( Table1[YEAR] ) - 1
        )
    )
RETURN
    Table1[Product Launch]
        - IF ( Table1[Month_Number] = 1, PreviousMonthValueforJan, PreviousMonthValue )

 

Regards
Zubair

Please try my custom visuals


View solution in original post

Thank you 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors