The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi there
I have a DAX calculating a particular product's price difference to last month.
Solved! Go to Solution.
Ok, have figured it out.
It was such a simple error, nothing to do with the DAX.
The data source is a spreadsheet, the date has been entered with the last date of the month which varies between 30th, 31st, 28th and 29th.
Once this was corrected to just month/year (mmm/yyyy) in the spreadsheet, missing months popped up.
Ok, have figured it out.
It was such a simple error, nothing to do with the DAX.
The data source is a spreadsheet, the date has been entered with the last date of the month which varies between 30th, 31st, 28th and 29th.
Once this was corrected to just month/year (mmm/yyyy) in the spreadsheet, missing months popped up.
Hey @Anonymous ,
I recommend reading this article, as it provides patterns to solve almost any date/time-related challenges: Time patterns – DAX Patterns
It's never a good idea to use a one-table solution, especially not when date/time-related challenges are involved, you might also want to read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
If the above articles do not to help to tackle your challenge, please take the time to create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix file to OneDrive, Google Drive, or dropbox and share the link. If you are using a spreadsheet to create the sample data, share the spreadsheet as well.
Do not forget to explain the expected result based on the sample data you provide.
Regards,
Tom
HI @TomMartens
Thank you for your reply.
I don't have a seperate calendar table set up for this calculation.
The Product, Month and Price are all coming from the same table.
Are you suggesting I create a calendar table for this calculation?
Hi,
Share some data, explain the question and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Hello @Anonymous ,
By understanding your requirements modified DAX a bit. Please try and let me know if it works or not.
Variance =
VAR monthOffset = 1
VAR _last =
MAXX(
FILTER(
'Table1',
'Table1'[Product] = EARLIER('Table1'[Product]) &&
'Table1'[Month] = EDATE(EARLIER('Table1'[Month]), - monthOffset)
),
'Table1'[Price]
)
RETURN
IF(
ISBLANK(_last) && monthOffset < 12, // Limiting to checking 12 months back
(
monthOffset = monthOffset + 1;
MAXX(
FILTER(
'Table1',
'Table1'[Product] = EARLIER('Table1'[Product]) &&
'Table1'[Month] = EDATE(EARLIER('Table1'[Month]), - monthOffset)
),
'Table1'[Price]
)
),
IF(ISBLANK(_last), BLANK(), 'Table1'[Price] - _last)
)
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
Hi @Kishore_KVN
Thank you for your reply.
No this DAX seems to be producing the same output.
Same months seems to be missing variance data
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
99 | |
81 | |
62 | |
54 |
User | Count |
---|---|
249 | |
119 | |
115 | |
94 | |
70 |