Difference between two columns and finding the sum of variance

[Anonymous]

Hi All,

I'm going to break my issue into two steps:

1. July being the first month of Fiscal year. I need to find the difference between value every month with July:

Eg: Month           Value          Difference

      July                  10              0

      Aug                  30              20

      Sep                   20             10

       Oct                  10              0

Something like above.

2. My second step is to find the sum of the difference column above.

Sum= 0+20+10+0=30

Any help would be appreciated.

Thanks in Advance.

Bhavya

[LivioLanzo]

Hi @Anonymous

are you able to post a sample of your data? is it aggregated by month?

[Anonymous]

Hi,

Thanks for your response. Below is how my data looks:

First I need to find the difference between each month data and July data and then find the sum of that difference to show it in another table.

YearMonth	Distribution
Jul-18	172406
Aug-18	168617
Sep-18	168061
Oct-18	145094
Nov-18	98080

[LivioLanzo]

Hello @Anonymous

are you looking to create a new calculated table with the data or in a matrix ?

[Anonymous]

In a Matrix table. 

[LivioLanzo · Accepted solution]

hello @Anonymous

try something like this and let me know if it works for yout scenario:

Distribution Difference = 

SUMX(
    SUMMARIZE( 'Calendar', 'Calendar'[Year], 'Calendar'[Year Month] ),
    CALCULATE( SUM( Distributions[Distribution] ) ) - 
    CALCULATE( 
        SUM( Distributions[Distribution] ), 
        STARTOFYEAR( 'Calendar'[Date], "06/30") 
    )
)

[Anonymous]

Thank You @LivioLanzo. Below formula worked for me. Something similar to what you have mentioned.

Distribution sku difference from JUL-2018 =

VAR __BASELINE_VALUE =

    CALCULATE(

        [Distribution sku Latest],

        'DIM_DATE'[YearMonth] IN { "JUL-2018" },

        ALL('DIM_DATE'[fiscalmonth])

    )

VAR __MEASURE_VALUE = [Distribution sku Latest]

RETURN

    if(max(DIM_DATE[YearMonth]) = "JUL-2018",[Distribution sku Latest],IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE))