Compare SamePeriod Sale

[PKGARG]

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%

[v-jiascu-msft]

@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]

PLEASE  CHECK MY DATA I HAVE LINKED MY DATA

https://1drv.ms/x/s!AqGdStzHrospaZSo2lTxFillv-k

[PKGARG]

I Need 2 Report Like This.

Report 1	According to Financial Year
Sum of Sale			Fin_Year
Loc_id	Loc_State	Loc_City	2015-2016	2016-2017	Sale Compare
11	HARYANA	SONIPAT	2850553		-100.00%
20	DELHI	NEW DELHI	23584820	25089441	6.38%
26	DELHI	NEW DELHI	4459689	3613806	-18.97%
37	DELHI	NEW DELHI	4786056	4512578	-5.71%
46	PUNJAB	AMRITSAR	5359835	4969098	-7.29%
48	RAJASTHAN	JAIPUR	12329267	12246815	-0.67%
51	UP	MEERUT	11795879	10915441	-7.46%
56	DELHI	NEW DELHI	27982833	25676666	-8.24%
84	DELHI	NEW DELHI	20050062	7717901	-61.51%
86	PUNJAB	JALANDHAR	6102958	6145344	0.69%
AC	PUNJAB	AMRITSAR	4121047	2879442	-30.13%
AL	JAMMU & KASHMIR	JAMMU	9480714	10413620	9.84%
AQ	WEST BENGAL	KOLKATTA	17794293	18481258	3.86%
AV	UNION TERR	CHANDIGHAR (UT)	5223224		-100.00%
Grand Total			155921231	132661410	-14.92%

[PKGARG]

Report 2	According To Same Period In Financial Year
Sum of Sale			Fin_Year
Loc_id	Loc_State	Loc_City	2015-2016	2016-2017	Sale Compare
11	HARYANA	SONIPAT			#DIV/0!
20	DELHI	NEW DELHI	23584820	25089441	6.38%
26	DELHI	NEW DELHI	4459689	3613806	-18.97%
37	DELHI	NEW DELHI	4786056	4512578	-5.71%
46	PUNJAB	AMRITSAR	5359835	4969098	-7.29%
48	RAJASTHAN	JAIPUR	12329267	12246815	-0.67%
51	UP	MEERUT	11795879	10915441	-7.46%
56	DELHI	NEW DELHI	27982833	25676666	-8.24%
84	DELHI	NEW DELHI	20050062	7717901	-61.51%
86	PUNJAB	JALANDHAR	6102958	6145344	0.69%
AC	PUNJAB	AMRITSAR	4121047	2879442	-30.13%
AL	JAMMU & KASHMIR	JAMMU	9480714	10413620	9.84%
AQ	WEST BENGAL	KOLKATTA	17794293	18481258	3.86%
AV	UNION TERR	CHANDIGHAR (UT)			#DIV/0!
Grand Total			147847454	132661410	-10.27%

[PKGARG]

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

[PKGARG]

ANYONE CAN HELP ME

[v-jiascu-msft · Accepted solution]

@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]

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]

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

[PKGARG]

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]

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]

Hi

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

[PKGARG]

Also i need one more comparison for only same store.

[Greg_Deckler]

SAMEPERIODLASTYEAR?

https://msdn.microsoft.com/en-us/library/ee634972.aspx

[PKGARG]

how can i attach my data