calculating dynamic change rate for month and year

[Anonymous]

hello.. I have aggregated data over year and month

year	month	region	size	value
2015	1	a	large	100
2015	2	a	large	90
2015	3	a	large	80
2015	4	a	large	90
2015	5	a	large	80
2015	6	a	large	76
2015	7	a	large	72
2015	8	a	large	68
2015	9	a	large	64
2015	10	a	large	60
2015	11	a	large	56
2015	12	a	large	52
2015	1	a	small	48
2015	2	a	small	44
2015	3	a	small	40
2015	4	a	small	36
2015	5	a	small	32
2015	6	a	small	28
2015	7	a	small	24
2015	8	a	small	20
2015	9	a	small	16
2015	10	a	small	12
2015	11	a	small	8
2015	12	a	small	4

I want to calculate value change over a year... also over a month .. dynamically group by the report column that end user will build

e.g.

size

year

month

change rate %

region

year

month

change rate %

size

year

change rate %

region

year

change rate %

can any one help with the dax equation I should use

[amitchandak]

@Anonymous , Create a date from year and month and then you can use time intelligence with datr table 

Date = Date([year],[month],1)

example

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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Fowmy]

@Anonymous 

Hope you have a calendar table in your model, if not create one using DAX or Power Query.
Following DAX formula can be used :

Sales MoM% = 
VAR __PREV_MONTH = CALCULATE(SUM('Table'[ Value]), DATEADD('Dates'[Date], -1, MONTH))
RETURN
	DIVIDE(SUM('Table'[ Value]) - __PREV_MONTH, __PREV_MONTH)

Sales YoY% = 
VAR __PREV_YEAR = CALCULATE(SUM('Table'[ Value]), DATEADD('Dates'[Date], -1, YEAR))
RETURN
	DIVIDE(SUM('Table'[ Value]) - __PREV_YEAR, __PREV_YEAR)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

hello mr fowmy...

thank you for you responde,,, but I mentioned that the data is aggregated by month.. so actually I don't have a day to relate with calendar table

[Fowmy]

@Anonymous 

I am not sure how your data model is built, anyhow can you try the following measure?

Rate % = 
var __Year = SELECTEDVALUE(Table01[year  ]) - 1 return
var __PreYr = 
    CALCULATE(
        SUM(Table01[value  ]), Table01[year  ] = __Year
    )
var __CurrYr =  
        SUM(Table01[value  ])
return
DIVIDE(__CurrYr - __PreYr , __PreYr)

It should work for all scenarios. 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn