Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yakovlol
Resolver I
Resolver I

Substruct Week vs. Previous Week in Matrix Column

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.

yakovlol_0-1725637617735.png

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

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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:

vlinyulumsft_0-1725876494652.png

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"

 

 

vlinyulumsft_1-1725876567436.png

 

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.

vlinyulumsft_2-1725876567438.png

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.

View solution in original post

Anonymous
Not applicable

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)

vlinyulumsft_1-1725957334620.png

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.

vlinyulumsft_2-1725957363783.png

 

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.

 

View solution in original post

7 REPLIES 7
yakovlol
Resolver I
Resolver I

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 

 

Anonymous
Not applicable

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:

vlinyulumsft_0-1725876494652.png

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"

 

 

vlinyulumsft_1-1725876567436.png

 

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.

vlinyulumsft_2-1725876567438.png

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.

Anonymous
Not applicable

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)

vlinyulumsft_1-1725957334620.png

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.

vlinyulumsft_2-1725957363783.png

 

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

 

Ritaf1983
Super User
Super User

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  

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.