Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Variance Percent Change – Current versus current month last year

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:

ShippingBook_Delivered
10/24/2019424,028
10/14/2019424,529
10/7/2019424,028
8/28/2019133,404
7/31/2019395,239
6/20/2019844,560
6/6/2019846,992
5/22/2019847,522
5/1/2019847,267
4/17/2019859,911
3/19/2019855,021
2/13/2019890,476
1/22/2019892,896
12/18/2018894,749
12/7/201830,752
11/28/2018904,343
10/27/2018456,948
11/7/2018907,243
9/18/2018901,813
7/19/2018904,519
6/14/2018905,212
5/17/2018864,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

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

5 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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's avatar
      Anonymous
      Not applicable
      6 years ago

      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