Variance Percent Change – Current versus current month last year

[Anonymous]

Hi everyone,

I'm trying to calculate the percentage variance of year to month for column B with with date range being selected from the slicer.

The goal: I want to show variance on how the book_delivery performed when some seleect a month versus the same month the previous year and the result be diplayed in a card. 

This is a sample of my data set:

Shipping	Book_Delivered
10/24/2019	424,028
10/14/2019	424,529
10/7/2019	424,028
8/28/2019	133,404
7/31/2019	395,239
6/20/2019	844,560
6/6/2019	846,992
5/22/2019	847,522
5/1/2019	847,267
4/17/2019	859,911
3/19/2019	855,021
2/13/2019	890,476
1/22/2019	892,896
12/18/2018	894,749
12/7/2018	30,752
11/28/2018	904,343
10/27/2018	456,948
11/7/2018	907,243
9/18/2018	901,813
7/19/2018	904,519
6/14/2018	905,212
5/17/2018	864,080

I try to use this formula, but the answer is not in percentage. Any help will be appriated:

Variance2_test = DIVIDE(

CALCULATE(

SUM('File for ID'[Book_Delivered]),

FILTER('Calendar','Calendar'[Date]=MAX('File for ID'[Book_Delivered])

)

),

CALCULATE(

SUM('File for ID'[Book_Delivered]),

FILTER('Calendar','Calendar'[Date]=MIN('File for ID'[Book_Delivered])))

,0)

- 1

I also tried this formula but I can't get the result to show in %

Test_last_MOM_MAIL_Delivered = CALCULATE(SUM('File for ID'[Book_Delivered]),DATEADD('Calendar'[Date],-1,YEAR))

Here is the pbix. data

https://drive.google.com/file/d/1q3A51hDrPb8a66BWNo_riu6InkuGzQ1G/view?usp=sharing

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Is this what you want?

Measure 2 = 
VAR x= 
CALCULATE(
    SUM(Sheet1[Book_Delivered]),
    SAMEPERIODLASTYEAR('Calendar'[Date])
)
var y = 
CALCULATE(
    SUM(Sheet1[Book_Delivered]),
    FILTER(
        Sheet1,
        YEAR(Sheet1[Shipping])
    )
)
RETURN
DIVIDE(
    x,y
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

For someone else who might look for a solution to the variance use the formula that @v-lionel-msft and add -1

Measure 2 = 
VAR x= 
CALCULATE(
    SUM(Sheet1[Book_Delivered]),
    SAMEPERIODLASTYEAR('Calendar'[Date])
)
var y = 
CALCULATE(
    SUM(Sheet1[Book_Delivered]),
    FILTER(
        Sheet1,
        YEAR(Sheet1[Shipping])
    )
)
RETURN
DIVIDE(
    x,y
)-1

[Anonymous]

@v-lionel-msft 

Thanks for answering this question. Is the Dax you wrote kind of similar to [This Period/Same Month last period]-1 

[v-lionel-msft]

Hi @Anonymous ,

The DAX formula means: 

This Period/Same Month last period.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft,

Thanks a lot. it did help