Forum Discussion

PKGARG's avatar
PKGARG
Helper I
9 years ago
Solved

Compare SamePeriod Sale

I have two table with Sale 2015-2016 and 2016-2017. In these table i have data of sale locationWise with datewise. Suppose Loc XJ Open 12-06-2015 now i need 1. Compare Sameperiod Sale,   for exampl...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    PKGARG

     

    Hi,

     

    In your situation, we aggregate data with two aspects: Date and Location and the data are separate in two tables. So we need two new tables if you didn’t have them.

     

    DateTable =
CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2017, 12, 31 ) ) 

     

    Locations =
DISTINCT (
    UNION (
        SUMMARIZE (
            '2015-2016',
            '2015-2016'[Loc_id],
            '2015-2016'[Loc_State],
            '2015-2016'[Loc_City]
        ),
        SUMMARIZE (
            '2016-2017',
            '2016-2017'[Loc_id],
            '2016-2017'[Loc_State],
            '2016-2017'[Loc_City]
        )
    )
)

    Then create relationship with the new table. The details are in the picture (upper).

    Create three measures with these formula.

     

    Sales2015-2016 =
CALCULATE (
    SUM ( '2015-2016'[Sale] ),
    SAMEPERIODLASTYEAR ( 'DateTable'[Date] )
)
    Sales2016-2017 =
SUM ( '2016-2017'[Sale] )
    Sales Compare =
( [Sales2016-2017] - [Sales2015-2016] )
/ [Sales2015-2016]

    Create report. Actually, your two reports are one due to we can control the period with the date slicer. Please have a try.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

PKGARG

 

Hi,

 

You can attach files with OneDrive or Dropbox and paste URL here. Maybe you could review this sample first. Greg_Deckler's SAMEPERIODLASTYEAR is a good idea.

First, create a date table to connect these two tables.

 

DateTable =
CALENDAR ( DATE ( 2015, 6, 1 ), DATE ( 2017, 4, 1 ) )

Second, establish relationship. (As showed in the picture.)

Third, Create measure.

 

Compare =
VAR LastSales =
    CALCULATE (
        SUM ( 'Sales2015-2016'[Sales1] ),
        SAMEPERIODLASTYEAR ( 'DateTable'[Date] )
    )
VAR CurrentSales =
    SUM ( 'Sales2016-2017'[Sales2] )
RETURN
    ABS ( LastSales - CurrentSales )
        / LastSales
 

We can create one more measure to check the result.

 

SalesInSamePeriodLastYear =
CALCULATE (
    SUM ( 'Sales2015-2016'[Sales1] ),
    SAMEPERIODLASTYEAR ( 'DateTable'[Date] )
)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

    • PKGARG's avatar
      PKGARG
      Helper I
      9 years ago

      I Need 2 Report Like This.

       

      Report 1According to Financial Year    
            
      Sum of Sale  Fin_Year  
      Loc_idLoc_StateLoc_City2015-20162016-2017Sale Compare
      11HARYANASONIPAT2850553 -100.00%
      20DELHINEW DELHI23584820250894416.38%
      26DELHINEW DELHI44596893613806-18.97%
      37DELHINEW DELHI47860564512578-5.71%
      46PUNJABAMRITSAR53598354969098-7.29%
      48RAJASTHANJAIPUR1232926712246815-0.67%
      51UPMEERUT1179587910915441-7.46%
      56DELHINEW DELHI2798283325676666-8.24%
      84DELHINEW DELHI200500627717901-61.51%
      86PUNJABJALANDHAR610295861453440.69%
      ACPUNJABAMRITSAR41210472879442-30.13%
      ALJAMMU & KASHMIRJAMMU9480714104136209.84%
      AQWEST BENGALKOLKATTA17794293184812583.86%
      AVUNION TERRCHANDIGHAR (UT)5223224 -100.00%
      Grand Total  155921231132661410-14.92%
      • PKGARG's avatar
        PKGARG
        Helper I
        9 years ago
        Report 2According To Same Period In Financial Year    
              
        Sum of Sale  Fin_Year  
        Loc_idLoc_StateLoc_City2015-20162016-2017Sale Compare
        11HARYANASONIPAT  #DIV/0!
        20DELHINEW DELHI23584820250894416.38%
        26DELHINEW DELHI44596893613806-18.97%
        37DELHINEW DELHI47860564512578-5.71%
        46PUNJABAMRITSAR53598354969098-7.29%
        48RAJASTHANJAIPUR1232926712246815-0.67%
        51UPMEERUT1179587910915441-7.46%
        56DELHINEW DELHI2798283325676666-8.24%
        84DELHINEW DELHI200500627717901-61.51%
        86PUNJABJALANDHAR610295861453440.69%
        ACPUNJABAMRITSAR41210472879442-30.13%
        ALJAMMU & KASHMIRJAMMU9480714104136209.84%
        AQWEST BENGALKOLKATTA17794293184812583.86%
        AVUNION TERRCHANDIGHAR (UT)  #DIV/0!
        Grand Total  147847454132661410-10.27%