Address "previous" column value for measures

[cerebro]

Dear community,

i would like to see the change in customer margins from month to month in a measure with the help of month number. 

The data is on a daily basis, previous month functions do not perform.

And ideally i would like to change the margin value into red or green if it goes up or down.

Can someone please help?

Thank you in advance.

[Anonymous · Accepted solution]

Hi @cerebro ,

Do you want to display Revenue per customer on a monthly basis and show the growth rate compared to the previous month?

Here is my test and attached pbix file for your reference.

Margin = VAR currentMonth = MONTH(MAX('Table'[Date]))
VAR lastMonth = currentMonth-1
VAR cust = MAX('Table'[Customer])
VAR lastRev = CALCULATE(SUM('Table'[Revenue]),ALL('Table'),MONTH('Table'[Date])=lastMonth&&'Table'[Customer]=cust)
RETURN IF(CONTAINS(ADDCOLUMNS(ALL('Table'[Date]),"Month",MONTH('Table'[Date])),[Month],lastMonth),(SUM('Table'[Revenue])-lastRev)/lastRev)

The conditional formatting.

Best regards,

Mengmeng Li

[johnt75]

Make sure that you have a properly formatted date table, marked as a date table. You can then create a measure like

Margin MoM % =
VAR CurrentMargin = [Margin]
VAR PrevMargin =
    CALCULATE ( [Margin], DATEADD ( 'Date'[Date], -1, MONTH ) )
VAR Result =
    DIVIDE ( CurrentMargin - PrevMargin, PrevMargin )
RETURN
    Result

[cerebro]

thank you very much, the formula works but only with month number. our date table looks like this and i can not change anything about it, the whole data model was created by an IT consultant and i can only create measure in a dashboard.

Can you please modify the formula that it contains month number or maybe you have an idea how i can adresse the date in the table from the sceenshot?

[johnt75]

I think that the formula I gave should still work, but if not you can try

Margin MoM % =
VAR CurrentMargin = [Margin]
VAR CurrentMonthNum =
    MAX ( 'Date'[Month] )
VAR PrevMargin =
    CALCULATE (
        [Margin],
        REMOVEFILTERS ( 'Date' ),
        VALUES ( 'Date'[Year] ),
        'Date'[Month] = CurrentMonthNum - 1
    )
VAR Result =
    DIVIDE ( CurrentMargin - PrevMargin, PrevMargin )
RETURN
    Result

[cerebro]

unfortunately, the formula doesnt work, it says that this line

    'Date'[Month] = CurrentMonthNum - 1

compares two different types of data, text and number.

is there any way to just address the previous column without the data table?