need help on cumulative values / multiple formulas

[Anonymous]

Hi Experts,

Need help on below data.

I need to write below formulas. Tried all ways, but not able to achieve.

Formula1: ((X2020/ Y2020) * Y2021) - X2021

                        Result: 

$    1,193

$           230

$        381

$    1,291

$        167

$      (368)

$      (730)

$  (1,278)

$      (696)

$  (2,374)

$      (944)

$      (250)

Formula2: (((JanX2020:febX2020)/ (JanY2020:FebY2020)) * (JanY2021:FebY2021)) - (JanX2021:FebX2021) - continue till Dec

Result:

$1,193

$        1,383

$    1,646

$    2,902

$    3,064

$    2,583

$    1,759

$        382

$      (418)

$  (2,864)

$  (3,805)

$  (4,057)

Formula3: (currenth month - preious month)

Jan

Feb-Jan

Mar-Feb

Apr-Mar

May-Apr

June-May

July-June

Aug-July

Sep-Aug

Oct-Sep

 Result:

$1,193

$           190

$        263

$    1,256

$        162

$      (481)

$      (824)

$  (1,377)

$      (800)

$  (2,446)

$      (941)

$      (252)

Final Outpt: (Formula3 / 1000) -  (showing single decimal)

Output
	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
A	(1.2)	(0.2)	(0.3)	(1.3)	(0.2)	0.5	0.8	1.4	0.8	2.4	0.9	0.3
B	0.0	(0.2)	(0.1)	(0.6)	(0.3)	(0.6)	(0.1)	0.2	0.3	0.1	(0.0)	0.1

Data / Input to power BI flle

Divison	year	Month	X	Y
A	2021	Jan	$       6,921	$  149,996
A	2021	Feb	$       6,497	$  138,298
A	2021	Mar	$       7,013	$  172,812
A	2021	Apr	$       7,212	$  164,285
A	2021	May	$       7,107	$  155,571
A	2021	Jun	$       7,164	$  159,607
A	2021	Jul	$       7,370	$  155,221
A	2021	Aug	$       7,758	$  154,091
A	2021	Sep	$       7,358	$  165,038
A	2021	Oct	$       7,667	$  161,951
A	2021	Nov	$       7,483	$  161,677
A	2021	Dec	$       7,324	$  155,816
A	2020	Jan	$       6,750	$  124,775
A	2020	Feb	$       6,184	$  127,142
A	2020	Mar	$       7,333	$  171,376
A	2020	Apr	$       7,235	$  139,798
A	2020	May	$       6,681	$  142,892
A	2020	Jun	$       7,059	$  165,789
A	2020	Jul	$       7,128	$  166,637
A	2020	Aug	$       7,157	$  170,188
A	2020	Sep	$       7,278	$  180,292
A	2020	Oct	$       5,401	$  165,253
A	2020	Nov	$       6,411	$  158,513
A	2020	Dec	$       6,921	$  152,446
B	2021	Jan	$           731	$    42,000
B	2021	Feb	$           518	$    33,003
B	2021	Mar	$           681	$    37,679
B	2021	Apr	$           614	$    44,388
B	2021	May	$           570	$    32,053
B	2021	Jun	$           679	$    42,603
B	2021	Jul	$           669	$    48,138
B	2021	Aug	$           694	$    38,014
B	2021	Sep	$           720	$    44,967
B	2021	Oct	$           691	$    44,137
B	2021	Nov	$           694	$    37,750
B	2021	Dec	$           800	$    49,233
B	2020	Jan	$           647	$    37,759
B	2020	Feb	$           606	$    27,740
B	2020	Mar	$           638	$    29,651
B	2020	Apr	$           719	$    22,099
B	2020	May	$           642	$    23,988
B	2020	Jun	$           862	$    29,397
B	2020	Jul	$           723	$    43,316
B	2020	Aug	$           659	$    40,381
B	2020	Sep	$           885	$    64,839
B	2020	Oct	$           776	$    50,503
B	2020	Nov	$           706	$    37,855
B	2020	Dec	$           711	$    48,953

[amitchandak · Accepted solution]

@Anonymous , with help from date table and time intelligence you can have YOY like

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

MOM

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Hi @amitchandak 

Thank you so much for your time and solution.

Humble request from my side, as I am new beginner and little confused on dates part / YTD / MTD, is it possible for you to help me by creating a  sample PBI file using my above data and send me here.

I know this is not the right thing to ask, but I am still not able to find a solution for my query.

Thanks again.

Thanks,

Raj