How to calculate monthly numbers from YTD values

[Amit_Saxena]

Hi ,
 Need help, if i have monthly YTD numbers against month, how can i get the montly data, here for example if i have Month, and YTD then how will i calculate Sales, pls suggest?
 
Month       Sales-?      YTD   
Month1       300       300   
Month2       400       700   
Month3       250       950 

Regard,
Amit

[Amit_Saxena]

Many thanks for your reply, this is how exactly my data:

Month	Country	YTD SALES	MONTHLY Sales
M1	A1	300	?
M1	A2	250	?
M1	A3	400	?
M2	A1	400	?
M2	A2	350	?
M2	A3	600	?
M3	A1	500	?
M3	A2	600	?
M3	A3	700	?

I need the monthly sales numbers, by using your formula i am not getting the desired result, Would ne great if you could help?

Regards,

Amit

[Ashish_Mathur]

Hi,

Will the month column actually have M1, M2 entries or will it have Jan, Feb etc.?  Share the actual entries that are there in the Month column.

[Amit_Saxena]

Thanks for your reply Ashish, These are actual Months no specific dates, i treid the date diff formula but it's not working.

[Ashish_Mathur]

As requested in my message earlier, share the actual entries in that column.

[Amit_Saxena]

Here are the values:

Date	Month	Country	YTD SALES	MONTHLY Sales
1/15/2020	Jan'20	US	300	?
1/15/2020	Jan'20	UK	250	?
1/15/2020	Jan'20	JPN	400	?
2/15/2020	Feb'20	US	400	?
2/15/2020	Feb'20	UK	350	?
2/15/2020	Feb'20	JPN	600	?
3/15/2020	Mar'20	US	500	?
3/15/2020	Mar'20	UK	600	?
3/15/2020	Mar'20	JPN	700	?

Regards,

Amit 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Amit_Saxena]

Thanks Ashish, Zoe & Amit for your responses and suggestions and apologies for responding too late on this but i am still struggling wiht the issue, may be some fundamental challenges, i might need to share some more dummy data with the actual senarios.

[dax]

Hi @Amit_Saxena , 

You could try below  measure to see whether it work or not.

Measure 2 = SUM(t2[YTD SALES])-CALCULATE(SUM(t2[YTD SALES]), FILTER(ALLEXCEPT(t2,t2[Country]),MIN(t2[Date])=DATEADD('t2'[date],1,MONTH)))

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Have new columns like

last Month= maxx(FILTER(Data, Data[Month]< EARLIER(Data[Month])),Data[Month])
Month Value = [YTD] - maxx(FILTER(Data, Data[Month]< EARLIER(Data[last Month])),Data[YTD])

[luapdoniv]

Check out this post:https://community.powerbi.com/t5/Desktop/Difference-between-two-rows/m-p/291854#M128593