Current, Previous and Forecast for the next month

[dkrishnan]

0Hi All

advance thanks for the help.

I have a situation where i need to derive the current full month forecast, previous month, mon change and mom change %

The table is as per below:-

Carrier	Traffic Period Start	Minutes
Carrier A	1-Apr-21	100
Carrier B	1-Apr-21	200
Carrier C	1-Apr-21	300
Carrier D	1-Apr-21	400
Carrier E	1-Apr-21	500
Carrier F	1-Apr-21	600
Carrier G	1-Apr-21	700
Carrier A	1-May-21	800
Carrier B	1-May-21	900
Carrier C	1-May-21	1000
Carrier D	1-May-21	1100
Carrier E	1-May-21	1200
Carrier F	1-May-21	1300
Carrier G	1-May-21	1400
Carrier A	1-Jun-21	1500
Carrier B	1-Jun-21	1600
Carrier C	1-Jun-21	1700
Carrier D	1-Jun-21	1800
Carrier E	1-Jun-21	1900
Carrier F	1-Jun-21	2000
Carrier G	1-Jun-21	2100

The focus is June'21. I need to filter the table to show only Jun'21 data having to derive:-

(a) The full month forecast for June'21. This is to be calculate based on run rate.

(b) What is the corresponding value of the previous month

(c) What is the change value & percentage between current and previous month on the same row of June'21

See sample where i need to be:-

Traffic Period	Carrier	Current Traffic Value	Current Month Forecast	Previous Month	Previous Month Vs Current	Previous Month Vs Current (%)	Previous Month Vs Forecast	Previous Month Vs Forecast (%)
1-Jun	Carrier A	1500	1,667	800	700	88%	967	121%
1-Jun	Carrier B	1600	1,778	900	700	78%	1,078	120%
1-Jun	Carrier C	1700	1,889	1000	700	70%	1,189	119%
1-Jun	Carrier D	1800	2,000	1100	700	64%	1,300	118%
1-Jun	Carrier E	1900	2,111	1200	700	58%	1,411	118%
1-Jun	Carrier F	2000	2,222	1300	700	54%	1,522	117%
1-Jun	Carrier G	2100	2,333	1400	700	50%	1,633	117%

Once i have completed the above, i would need to have a card that shows the carrier that had the lowest movement between last month to current month. Some may show negative movememt.

I tried few methods but as soon as i filter the current month, the columns become blank.

appreciate any help here

regards

deven

[dkrishnan]

Hi

Thanks for the feedback. I am able to solve the filter issue. However, for the previous month, the value i am getting still the same as the current month. Any thoughts on that?

regards

deven

[HashamNiaz · Accepted solution]

Hi @dkrishnan !

Since the calculation uses Date / Calendar dimension, to calculate Previous Month, you need to Place [Calendar Date] / [Calendar Month] in your Matrix visual instead of [Traffic Period], this will resolve the issue.

Regards,

Hasham

[dkrishnan]

yep. it resolved as mentioned @HashamNiaz . thanks for the help.

[HashamNiaz]

Hi @dkrishnan !

Here are some of teh measure you can use to calculate Previous Month  vs MoM %, you can replicte this for Forecast scenario as well;

Traffic Value = SUM(Table[StartMinutes])

Previous Month Traffic Value = CALCULATE([Traffic Value], DATEADD('Calendar'[Date], -1, MONTH))

Previuos Month Vs Current = [Traffic Value] - [Previous Month Traffic Value]

Previuos Month Vs Current % = DIVIDE( ([Traffic Value] - [Previous Month Traffic Value]), [Previous Month Traffic Value])

Measures;

[Traffic Value] is calculating trafic value in current context

[Previous Month Traffic Value] is clauclating traffic value for prevous month

[Previous Month Vs Current] is caluclating difference between Current month vs Previous month traffic values

[Previous Month Vs Current %] is calculating MoM% increase between previous vs current month

Regards,

Hasham