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 example, XJ Sale 12-06-2015 to 31-03-2016 is 11005880 And 12-06-2016 to 31-03-2017 is 12180408 then Result for New Column (Compare) is 10.67%

 

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

15 Replies

  • 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%