The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have data that is brought in via monthly reports like so:
Date | ID | Name | Amount_Held |
09-2024 | 1 | Cust_1 | 100 |
09-2024 | 2 | Cust_2 | 100 |
09-2024 | 3 | Cust_3 | 200 |
10-2024 | 2 | Cust_2 | 300 |
10-2024 | 3 | Cust_3 | 400 |
10-2024 | 4 | Cust_4 | 500 |
10-2024 | 5 | Cust_5 | 500 |
I would like to create columns that bring in the amounts from the previous amounts. I have been able to successfully do this, but the problem is that it doesn't not bring in records that don't have amounts in the latest month. So, for example, the first row would not show if I created a table visual that pulls the latest report date and would not capture the total previous amount accurately.
Example of what I would want:
Date | ID | Name | Amount_Held | Previous_Amount |
10-2024 | 1 | Cust_1 | 0 | 100 |
10-2024 | 2 | Cust_2 | 300 | 100 |
10-2024 | 3 | Cust_3 | 400 | 200 |
10-2024 | 4 | Cust_4 | 500 | 0 |
10-2024 | 5 | Cust_5 | 500 | 0 |
Solved! Go to Solution.
Hi @botaac, thank you for your feedback.
Can you please try this alternative:
1. Create a CustomerDateTable (to include all unique combinations of customers and Year-Months)
CustomerDateTable =
CROSSJOIN(
DISTINCT('MainTable'[ID]),
DISTINCT(DateTable[YearMonth])
)
2. Create a FinalTable (to ensure that if a customer was missing from a month, their amount for that month shows as zero)
FinalTable =
ADDCOLUMNS(
CustomerDateTable,
"Date", MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date]),
"Name", LOOKUPVALUE('MainTable'[Name], 'MainTable'[ID], CustomerDateTable[ID]),
"Amount_Held", COALESCE(
LOOKUPVALUE('MainTable'[Amount_Held], 'MainTable'[ID], CustomerDateTable[ID], 'MainTable'[Date],
MAXX(FILTER('MainTable', 'MainTable'[Date] = EOMONTH(DateTable[Date], 0)), 'MainTable'[Date])
),
0
),
"Previous_Amount",
VAR CurrentMonth = MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date])
RETURN COALESCE(
CALCULATE(
SUM('MainTable'[Amount_Held]),
FILTER(
'MainTable',
'MainTable'[ID] = CustomerDateTable[ID] &&
'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
)
),
0
)
)
Hope this helps 🙂
Hello @botaac,
Can you please try the following:
1. First, ensure Your Data Model Has a Date Table
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
"YearMonth", FORMAT([Date], "YYYYMM"),
"Year", YEAR([Date]),
"Month", MONTH([Date])
)
2. Create a measure that retrieves the previous month’s amount for each customer
Previous_Amount =
VAR CurrentMonth = MAX('MainTable'[Date])
RETURN
CALCULATE(
SUM('MainTable'[Amount_Held]),
FILTER(
'MainTable',
'MainTable'[ID] = EARLIER('MainTable'[ID]) &&
'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
)
)
3. Handle Zeros for Missing Values
Previous_Amount =
VAR CurrentMonth = MAX('MainTable'[Date])
RETURN
COALESCE(
CALCULATE(
SUM('MainTable'[Amount_Held]),
FILTER(
'MainTable',
'MainTable'[ID] = EARLIER('MainTable'[ID]) &&
'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
)
),
0
)
Hope this helps.
Hi Sahir, Thanks so much for the response. This is helpful.
Luckily I was able to get this far and accomplish this. The issue that I am running into is related to the records that aren't in the latest month.
For example, Cust_1 is in 9-2024 but not in 10-2024. We would want to capture the fact that Cust_1 is now 0 in 10-2024. Is this possible?
I don't think your proposed solution would account for this but please correct me if I am wrong and thank you so much for your time 🙂
Hi @botaac, thank you for your feedback.
Can you please try this alternative:
1. Create a CustomerDateTable (to include all unique combinations of customers and Year-Months)
CustomerDateTable =
CROSSJOIN(
DISTINCT('MainTable'[ID]),
DISTINCT(DateTable[YearMonth])
)
2. Create a FinalTable (to ensure that if a customer was missing from a month, their amount for that month shows as zero)
FinalTable =
ADDCOLUMNS(
CustomerDateTable,
"Date", MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date]),
"Name", LOOKUPVALUE('MainTable'[Name], 'MainTable'[ID], CustomerDateTable[ID]),
"Amount_Held", COALESCE(
LOOKUPVALUE('MainTable'[Amount_Held], 'MainTable'[ID], CustomerDateTable[ID], 'MainTable'[Date],
MAXX(FILTER('MainTable', 'MainTable'[Date] = EOMONTH(DateTable[Date], 0)), 'MainTable'[Date])
),
0
),
"Previous_Amount",
VAR CurrentMonth = MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date])
RETURN COALESCE(
CALCULATE(
SUM('MainTable'[Amount_Held]),
FILTER(
'MainTable',
'MainTable'[ID] = CustomerDateTable[ID] &&
'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
)
),
0
)
)
Hope this helps 🙂