CODEWITHSUNDEEP
dax
simon
simon

Reputation: 139

Power BI DAX to add extra row for each customer after their last transaction, but increment their date by a month

I want to get a subset of this table that includes the last transaction record for each customer (in this case the bolded rows), increment the OrderDate by 1 month, then append this to the existing table.

here is my example table

Name Date Amt
CUSTA Jan 2023 10
CUSTA Feb 2023 10
CUSTA Mar 2023 10
CUSTB Nov 2022 10
CUSTB Dec 2022 10
CUSTB Jan 2023 10
CUSTB Feb 2023 10
CUSTB Mar 2023 10
CUSTC Nov 2022 10
CUSTC Dec 2022 10
CUSTC Jan 2023 10

the additional rows to add would be

CUSTA Apr 2023 10
CUSTB Apr 2023 10
CUSTC Feb 2023 10

Final step would then to set the Amt to zero for these rows.

It should have been very straight forward, but i have struggled.

I have managed to get a subset (the bolded 3 rows) with a filter function

FILTER(table,'table'[Date] = CALCULATE(MAX('table'[Date]), ALLEXCEPT('table', 'table'[Name]))), but struggled to increment the Date (tried DateAdd), but it seems that if the filtered table is in a variable, it does recognises the columns.

THis is part of a process to calc the Status of each customer at any given month (no change, increase, decrease, Churn, New), which i would implement by comparing Amt with previous month's value (assumed the table will be sorted by customer and date)

Any help would be much appreciated!

Upvotes: 0

Views: 111

Answers (1)

kbj
kbj

Reputation: 21

My suggestion is to compare sales compared to previous month using a measure like this one instead, which could be modified to express if the customer is new etc as well.

Sales compared to last month = 
VAR current_month = MAX('Calendar'[DateKey])
VAR sales_current_month =
    CALCULATE(
        sum(Sales[SalesAmount]),
        VALUES('Calendar'[Year]),
        VALUES('Calendar'[MonthName])
    )
VAR sales_previous_month =
    CALCULATE(
        sum(Sales[SalesAmount]),
        PREVIOUSMONTH('Calendar'[DateKey])
    )
VAR result_for_month_level_only =
    IF(
        ISFILTERED('Calendar'[MonthName]),
        sales_current_month - sales_previous_month
    )
RETURN result_for_month_level_only

result

Upvotes: 0

Related Questions