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

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%
  • PKGARG's avatar
    PKGARG
    Helper I
    9 years ago

    also i need obtion i can view this report Monthwise, Financial Year Wise, Statewise

    • PKGARG's avatar
      PKGARG
      Helper I
      9 years ago

      ANYONE CAN HELP ME

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        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