cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuliaWendel Frequent Visitor
Frequent 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

Accepted Solutions
Super User III
Super User III

Re: Calculation within a Column

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 )

 

Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Calculation within a Column

Microsoft v-ljerr-msft
Microsoft

Re: Calculation within a Column

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

Re: Calculation within a Column

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 )

 

Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Calculation within a Column

Microsoft v-ljerr-msft
Microsoft

Re: Calculation within a Column

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

JuliaWendel Frequent Visitor
Frequent Visitor

Re: Calculation within a Column

Thank you 🙂

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors