Get previous month data (not aggregate/numeric)

[mocha911]

Hi folks, 

I am a newbie in power bi and am creating a visual and need to get the "previous month's product" as below -

Date	ClientNo	Current Product	Prev Month Product
Tuesday, 31 January 2023	1	A
Tuesday, 28 February 2023	1	A	A
Friday, 31 March 2023	1	A	A
Sunday, 30 April 2023	1	A	A
Tuesday, 2 May 2023	1	B	A
Wednesday, 31 May 2023	1	B	A
Friday, 30 June 2023	1	B	B
Monday, 31 July 2023	1	B	B
Thursday, 31 August 2023	1	B	B
Saturday, 30 September 2023	1	B	B

Do you have any suggestion what I should do?
a bit more info of this dataset - it is a power bi dataset so I can't edit any relationship in the tables. there is a date table, client table and product table. 
I can't add columns into the table either.

So I am thinking to create a measure using "Calculate". This is what I tried - Calculate(max(Product_table[Current Product]), previousmonth(Date_Table[Date]))
this gives me the following result   -

Date	ClientNo	Current Product	Prev Month Product
Tuesday, 31 January 2023	1	A
Tuesday, 28 February 2023	1	A	A
Friday, 31 March 2023	1	A	A
Sunday, 30 April 2023	1	A	A
Friday, 30 June 2023	1	B	B
Monday, 31 July 2023	1	B	B
Thursday, 31 August 2023	1	B	B

The month of change (May 2023) is missing in the table which I don't understand why.

Can anyone please help me? thank you

[123abc · Accepted solution]

It looks like you're trying to create a measure in Power BI to show the previous month's product for each client. The issue you're facing might be related to how the relationships between tables are set up in your data model.

Since you mentioned that you can't edit any relationships in the tables, you can try using a combination of CALCULATETABLE and FILTER functions to achieve the desired result. Here's an example measure that you can try:

Prev Month Product =
VAR CurrentDate = MAX(Date_Table[Date])
VAR PrevMonth = CALCULATETABLE(ALL(Date_Table), DATEADD(Date_Table[Date], -1, MONTH))
RETURN
CALCULATE(
VALUES(Product_table[Current Product]),
FILTER(
ALL(Client_Table),
Client_Table[ClientNo] = VALUES(Client_Table[ClientNo])
),
FILTER(
PrevMonth,
Date_Table[Date] = CurrentDate
)
)

his measure uses a variable to store the current date, then uses CALCULATETABLE to get a table of all dates from the previous month. Finally, it uses CALCULATE with appropriate filters to retrieve the previous month's product for each client.

Please replace Client_Table with the actual name of your client table, and make sure that the column names used in the relationships are correct. Adjust the measure accordingly based on your specific table and column names.

If the issue persists or if you provide more information about your data model, I can further assist you in troubleshooting the problem.

[ThxAlot · Accepted solution]

For fun only, a showcase of powerful Excel formulas,

[123abc · Accepted solution]

It looks like you're trying to create a measure in Power BI to show the previous month's product for each client. The issue you're facing might be related to how the relationships between tables are set up in your data model.

Since you mentioned that you can't edit any relationships in the tables, you can try using a combination of CALCULATETABLE and FILTER functions to achieve the desired result. Here's an example measure that you can try:

Prev Month Product =
VAR CurrentDate = MAX(Date_Table[Date])
VAR PrevMonth = CALCULATETABLE(ALL(Date_Table), DATEADD(Date_Table[Date], -1, MONTH))
RETURN
CALCULATE(
VALUES(Product_table[Current Product]),
FILTER(
ALL(Client_Table),
Client_Table[ClientNo] = VALUES(Client_Table[ClientNo])
),
FILTER(
PrevMonth,
Date_Table[Date] = CurrentDate
)
)

his measure uses a variable to store the current date, then uses CALCULATETABLE to get a table of all dates from the previous month. Finally, it uses CALCULATE with appropriate filters to retrieve the previous month's product for each client.

Please replace Client_Table with the actual name of your client table, and make sure that the column names used in the relationships are correct. Adjust the measure accordingly based on your specific table and column names.

If the issue persists or if you provide more information about your data model, I can further assist you in troubleshooting the problem.

[mocha911]

Thank you so much for the solution! it almost works but the final output misses the "May" month which is the month of the change happened. I am guessing because there are two entries (one is not month end) in May which stuffed up the dax. Any solutions for this? As it will always happens on the month of change.