topic Re: Address "previous" column value for measures in DAX Commands and Tips

Address "previous" column value for measures

cerebro — Tue, 12 Nov 2024 12:35:00 GMT

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.

Re: Address "previous" column value for measures

johnt75 — Tue, 12 Nov 2024 13:36:39 GMT

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

Re: Address "previous" column value for measures

cerebro — Tue, 12 Nov 2024 14:15:29 GMT

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?

Re: Address "previous" column value for measures

johnt75 — Tue, 12 Nov 2024 14:25:51 GMT

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

Re: Address "previous" column value for measures

cerebro — Tue, 12 Nov 2024 15:01:48 GMT

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?

Re: Address "previous" column value for measures

Anonymous — Fri, 15 Nov 2024 06:25:17 GMT

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