how to get previous data to calculate

[Anonymous]

hi, here is the data looks like:

yearmonth value

202112      8

202201      9

202202      12

I want to calculate the gap with selected current yearmonth and previous one, for example:

if selected value is 202202, then gap=12-9=3,

if selected value is 202201, then gap=9-8=1,

I have no idea to decide how to get vlue that yearmonth is 202112 when filter is 202201

do you have any ideas to solve it? Thanks

[tamerj1]

Hi @Anonymous 

you neef to create a new calculated column [Year Month Sequential Number] which is RANKX ( TableName, TableName[Year Month],, ASC, Dense )

then the previous period would be 

CALCULATE ( SUM ( TableName[Value], TableName[Year Month Sequential Number] = MAX ( TableName[Year Month Sequential Number] ) - 1, ALL ( TableName[Year Month] ) )

[MahyarTF · Accepted solution]

Hi,

Create below Measure and use it in your visuals :

_GapValue =

Var _PreviousValue = CALCULATE( sum(Sheet132[Value]),

                               filter(all(Sheet132),

                                      Sheet132[YearMonth] =

                                            CALCULATE(max(Sheet132[YearMonth]),

                                                      filter(all(sheet132),

                                                             Sheet132[YearMonth] < SELECTEDVALUE( Sheet132[YearMonth])

                                                             )

                                                    )

                                    )

                                )

Var _CurrentValue = CALCULATE( sum(Sheet132[Value]))

Return if (_PreviousValue > 0 , _CurrentValue - _PreviousValue)

Appreciate your Kudos

[amitchandak]

@Anonymous , You can create a date from this 

Date = date(right([yearmonth],4) , left([yearmonth],2) , 1) 

Then you can join this with date table

and you can use TI

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]))

this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

You can have measures with help from month rank column

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Where month rank is a column in date/yearmonth table

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

or

Month Rank = RANKX(all('Date'),'Date'[YearMonth],,ASC,Dense)

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