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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
Employee
Employee

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

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

Thank you 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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