Cumulative comparison for Month vs Month

[Kannan4444]

Hey all,

Help need to see Cumulative trend by comparing Current month, vs prior month and same period vs previous year.

tried various formula from

Cumulative 1 = CALCULATE(SUM(BaselineView[KT]),DATESMTD('Load Calendar'[Load date]))

Cumulative 2 = CALCULATE(SUM(BaselineView[KT]),FILTER(ALLSELECTED(BaselineView),BaselineView[LoadDate] <= MAX(BaselineView[LoadDate])))

Cumulative 3 = CALCULATE([Total sales LM],FILTER( ALL('Load Calendar'),'Load Calendar'[Load date]<='Load Calendar'[Load date]))

Created Mearure to calculate Previous month

Total sales LM = CALCULATE([Total KT], DATEADD('Load Calendar'[Load date],-1,MONTH))

Please help me Guys 

Date	Volume	Cumulative volume	load place	discharge place
01-Jan-18	111	111	Oman	LoadSubregion
02-Jan-18	47	158	0	AG India
03-Jan-18	94	252	USA	N/A
04-Jan-18	67	319	USA	North America
05-Jan-18	39	358	Indonesia	North America
06-Jan-18	33	391	Indonesia	SE Asia
07-Jan-18	36	427	Romania	SE Asia
08-Jan-18	35	462	South Korea	MED
09-Jan-18	75	537	Canada	NE Asia Oz
10-Jan-18	17	554	Canada	North America
11-Jan-18	50	604	Angola	North America
12-Jan-18	35	639	Brazil	WAF SAF
13-Jan-18	61	700	Brazil	Latam
14-Jan-18	50	750	Canada	Latam
15-Jan-18	34	784	Canada	North America
16-Jan-18	32	816	Canada	North America
17-Jan-18	67	883	China	North America
18-Jan-18	51	934	Greece	China
19-Jan-18	65	999	Greece	MED
20-Jan-18	42	1,041	India	MED
21-Jan-18	83	1,124	India	AG India
22-Jan-18	67	1,191	Lithuania	AG India
23-Jan-18	10	1,201	Mexico	FSU
24-Jan-18	9	1,210	Mexico	Latam
25-Jan-18	28	1,238	Singapore	Latam
26-Jan-18	60	1,298	Singapore	SE Asia
27-Jan-18	96	1,394	Singapore	SE Asia
28-Jan-18	54	1,448	Taiwan, Republic of China	SE Asia
29-Jan-18	94	1,542	Taiwan, Republic of China	NE Asia Oz
30-Jan-18	50	1,592	USA	NE Asia Oz
31-Jan-18	31	1,623	USA	North America
01-Feb-18	122	122	South Korea	North America
02-Feb-18	58	180	USA	NE Asia Oz
03-Feb-18	85	265	USA	North America
04-Feb-18	11	276	Australia	North America
05-Feb-18	57	333	Australia	NE Asia Oz
06-Feb-18	48	381	Belgium	NE Asia Oz
07-Feb-18	53	434	Belgium	NWE
08-Feb-18	36	470	Belgium	NWE
09-Feb-18	3	473	Greece	NWE
10-Feb-18	74	547	Greece	MED
11-Feb-18	90	637	India	MED
12-Feb-18	55	692	India	AG India
13-Feb-18	97	789	Romania	AG India
14-Feb-18	63	852	Romania	MED
15-Feb-18	7	859	Russian Federation	MED
16-Feb-18	9	868	Singapore	FSU
17-Feb-18	82	950	Singapore	SE Asia
18-Feb-18	66	1,016	South Korea	SE Asia
19-Feb-18	82	1,098	South Korea	NE Asia Oz
20-Feb-18	41	1,139	South Korea	NE Asia Oz
21-Feb-18	92	1,231	South Korea	NE Asia Oz
22-Feb-18	80	1,311	South Korea	NE Asia Oz
23-Feb-18	12	1,323	Spain	NE Asia Oz
24-Feb-18	53	1,376	Thailand	MED
25-Feb-18	89	1,465	USA	SE Asia
26-Feb-18	24	1,489	USA	North America
27-Feb-18	51	1,540	USA	North America
28-Feb-18	20	1,560	USA	North America
01-Mar-18	133	133	USA	North America
02-Mar-18	97	230	USA	North America
03-Mar-18	76	306	USA	North America
04-Mar-18	66	372	USA	North America
05-Mar-18	81	453	Canada	North America
06-Mar-18	70	523	Canada	North America
07-Mar-18	77	600	Canada	North America
08-Mar-18	37	637	USA	North America
09-Mar-18	87	724	South Korea	North America
10-Mar-18	74	798	South Korea	NE Asia Oz
11-Mar-18	73	871	India	NE Asia Oz
12-Mar-18	64	935	Indonesia	AG India
13-Mar-18	97	1,032	Indonesia	SE Asia
14-Mar-18	80	1,112	Portugal	SE Asia
15-Mar-18	69	1,181	Russian Federation	NWE
16-Mar-18	90	1,271	United Kingdom	FSU
17-Mar-18	41	1,312	United Kingdom	NWE
18-Mar-18	14	1,326	USA	NWE
19-Mar-18	72	1,398	USA	North America
20-Mar-18	3	1,401	Australia	North America
21-Mar-18	71	1,472	Indonesia	NE Asia Oz

[Kannan4444]

Actually I have 5 years of data. Just to give am example i kept the data limited.

Agree the dates differ, Is there a way to see the previous month data. 🤔

[lbendlin]

have you considered my second point? What is your decision?

[Kannan4444]

It will not be 100% match,but it helps us to see the comparions. 

I also have a pbix uploaded in the below link,

https://1drv.ms/u/s!AhLDlY0KU7uTavcpBl9vRheZLX4?e=3hMC8s

[lbendlin]

Before you do your comparison you may want to adjust your data model.  Here is how I would structure it

A single calendar table with an active connection to Load date and an inactive connection to Discharge date

Same for the geography dimension. Technically you should get rid of the port column as it is not joined to the fact table. If you make country the primary key you can then link it the same way - actively to Load country and inactively to Discharge country.

With that type of data model your computations will be much easier.

[Kannan4444]

Hey Guys, Any help please...!!!!

https://1drv.ms/u/s!AhLDlY0KU7uTavcpBl9vRheZLX4?e=3hMC8s

[Kannan4444]

Sure thanks for that. Can you please help me with the Measure how to bring the Cumulative for comparison.

[lbendlin]

There are a couple of complications here that you need to consider

- your sample data does not contain last year's data

- for a month over month comparison you need to realize that January has 31 days, February has 28 days and March has 21 days (in your sample data).  Comparing these is "not fair".