Work out % of increase or decrease by period

[craig811]

Hi,

I am sure this is an easy one, however do I write a measure to show the difference % between periods.

Period           Adjustment value

Jan-20                 30,0000

Feb-20               75,0000

Mar-20              55,0000

So I need to write a measure to show the difference in % of my Adjustment value, so Feb-20 would be and increase from Jan-20 and Mar-20 would be a decrease from Feb-20.

Many thanks

[Ashish_Mathur]

Hi,

Try this approach:

1. Create a Calendar Table and extract Year, month name and month number in seperate columns: Year = year (Calendar[Date]), Month Name = FORMAT(Calendar[Date],"mmmm"), Month number = month(Calendar[Date]).  Sort the Month Name by the Month number
2. Create a relationship from the Date column of your source data to the Date column of the Calendar Table
3. To your visual, drag the Year and Month name from the Calendar Table
4. Write these measures

Adjustment value = SUM(Data[Adj value])

Adjustment value in previous month = CALCULATE([Adjustment value],PREVIOUSMONTH(Calendar[Date]))

Growth in adjustment value over previous month = IFERROR([Adjustment value]/[Adjustment value in previous month]-1,BLANK())

Hope this helps.

[dax]

Hi @craig811  

You also could try to refer to below expression. Use M code to change period to date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krM0zUyUNJRMjYAAaVYnWglt9QkiJi5KVzMN7EIImYKFYsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, #"Adjustment value" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Period1", each "01-"&[Period]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Period"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Period1", type date}, {"Adjustment value", Int64.Type}})
in
    #"Changed Type"

 Then try below measure

Measure = var pre=CALCULATE(SUM('Table'[Adjustment value]), FILTER(ALLSELECTED('Table'[Period1]), MIN('Table'[Period1])=DATEADD('Table'[Period1],1, MONTH))) return if(pre=BLANK(), 0,(SUM('Table'[Adjustment value])-pre)/pre)

 You could refer to my sample for details.

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]

You can use time intelligence with date calendar. datesmtd and totalmtd can you that easily

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 MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

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

In case you only have month in given format make it date like

Date = "01-" & [Month] // change data type to date

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[MFelix]

Hi  @craig811 ,

You need to create a measure similar to this one:

% increase / decrease =
DIVIDE (
    SUM ( 'Table'[Adjustment Value] );
    CALCULATE (
        TOTALMTD (
            SUM ( 'Table'[Adjustment Value] );
            DATEADD ( 'Table'[Period]; -1; MONTH )
        )
    )
) - 1