Forum Discussion
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 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.
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
5 Replies
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
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 Resultthank 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?
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
