Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello community!
Can you please help to write the correct dax
I have such a matrix. And what is needed I need to have a difference between this week and the previous week.
Availability in week 23 should be compared with availability in previous week 22. Week 23 with week 22.
The problem is that this week numbers are text columns, because i need to have matrix in such way.
But I need to have difference between Avilibility vs Avilibilty Week vs Week.
Any ideas how to manage it within DAX?
Thank you
Solved! Go to Solution.
hanks for the reply from Ritaf1983 , please allow me to provide another insight:
Hi, @yakovlol
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.I offer two ways to distinguish between Avilibilty Week and Week:
The first is to use the right() function and the left() function, but this method is prone to circular dependencies when called later:
Column =
IF (
LEFT ( 'Table'[Column2], 4 ) = "Week",
RIGHT ( 'Table'[Column2], 2 ) & "01",
RIGHT ( 'Table'[Column2], 2 ) & "02"
)
The second option is to create a custom column like this:
This will make it easier for you to manage your matrix:
if Text.Start([Column2],4)="Week" then Text.End([Column2],2)&"01" else Text.End([Column2],2)&"02"
You may want to note that both methods require you to convert the data type
Regarding your data, I can't open it for the time being.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @yakovlol
Thanks for the quick reply.
First of all, according to our current security protocols, you can't open the data of the Google page temporarily, you can try the following link to upload the data:
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Secondly, according to your needs, I propose the following solutions:
1.First, you need to create the following index column in PowerQuery:
if Text.Start([Column2],4)="Week" then "01"&Text.End([Column2],2) else "02"&Text.End([Column2],2)
2.Second, create the following calculated columns:
diff =
VAR ni='Table'[index]
RETURN 'Table'[values]-CALCULATE(SUM('Table'[values]),FILTER(ALL('Table'),'Table'[index]=ni-1))
4.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Ritaf1983 Thanks for your reply
I created some dummy data. example.pbix
And what is expected is to have a matrix with the difference between 2-week Avilibility vs Aviliavility, just week vs just week
hanks for the reply from Ritaf1983 , please allow me to provide another insight:
Hi, @yakovlol
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.I offer two ways to distinguish between Avilibilty Week and Week:
The first is to use the right() function and the left() function, but this method is prone to circular dependencies when called later:
Column =
IF (
LEFT ( 'Table'[Column2], 4 ) = "Week",
RIGHT ( 'Table'[Column2], 2 ) & "01",
RIGHT ( 'Table'[Column2], 2 ) & "02"
)
The second option is to create a custom column like this:
This will make it easier for you to manage your matrix:
if Text.Start([Column2],4)="Week" then Text.End([Column2],2)&"01" else Text.End([Column2],2)&"02"
You may want to note that both methods require you to convert the data type
Regarding your data, I can't open it for the time being.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello! @Anonymous thanks for your input. This solution wasn't sold my problem because of different data structure. But it gave me an idea that i implemented in my data set and it works
@Anonymous Sorry I didn't get your answer. What want to have is a difference between weeks. As example
Week 23 Value 100, Week 24 Value 90. And What is need that in week 24 we have -10 etc
the same for week Avilibility 23 Value 75, Avilibility 24 Value 175 and expected result for Column Avilibility 24 Value 100
The problem is that it's all in one table, an i cannt get how to do such comparrison with previous value.
Hi, @yakovlol
Thanks for the quick reply.
First of all, according to our current security protocols, you can't open the data of the Google page temporarily, you can try the following link to upload the data:
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Secondly, according to your needs, I propose the following solutions:
1.First, you need to create the following index column in PowerQuery:
if Text.Start([Column2],4)="Week" then "01"&Text.End([Column2],2) else "02"&Text.End([Column2],2)
2.Second, create the following calculated columns:
diff =
VAR ni='Table'[index]
RETURN 'Table'[values]-CALCULATE(SUM('Table'[values]),FILTER(ALL('Table'),'Table'[index]=ni-1))
4.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Anonymous Can you please try another file? I tried to save in another space
https://drive.google.com/file/d/10FZsJxlEDpB4BFvjo93qMM8MfpnpooIl/view?usp=sharing
Hi @yakovlol
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
User | Count |
---|---|
73 | |
69 | |
35 | |
27 | |
26 |
User | Count |
---|---|
96 | |
92 | |
54 | |
45 | |
41 |