Forum Discussion
Get previous month data (not aggregate/numeric)
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.
For fun only, a showcase of powerful Excel formulas,
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.
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.