Forum Discussion

cerebro's avatar
cerebro
Icon for Helper I rankHelper I
1 year ago
Solved

Address "previous" column value for measures

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 ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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's avatar
    cerebro
    Icon for Helper I rankHelper I
    1 year ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
        cerebro
        Icon for Helper I rankHelper I
        1 year ago

        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?