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

Ist of All Thanks,

Everything is may be ok, but i visualised data then Sales2015-2016 value not shown in Table and SalesCompare show infinity. When i changed by

Sales2015-2016=sum('2015-2016'[Sale]) its working and SalesCompare Result OK, Now Date Slicer is not Working. Pls see where i wrong....

PKGARG's avatar
PKGARG
Helper I
9 years ago

Also i want to add Month Slicer to see on Month or Multiple Month Status

  • PKGARG's avatar
    PKGARG
    Helper I
    9 years ago

    I try on data provided by me Its Working Sorry Dale, I am trying again where i am wrong. Again Thank You Very Much.

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

    Hi,

     

    It's my pleasure. I am so glad it helped. Maybe you need to make a few changes on the formula. 

     

     

    Best Regards!

    Dale

  • PKGARG's avatar
    PKGARG
    Helper I
    9 years ago

    Hi

    In My Same Report i want view it MonthWise according to financial year like firstly Apr,May,Jun,Jul.......

  • PKGARG's avatar
    PKGARG
    Helper I
    9 years ago

    Also i need one more comparison for only same store.