Substruct Week vs. Previous Week in Matrix Column

[yakovlol]

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

[Anonymous · Accepted 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.

[yakovlol]

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 · Accepted 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.

[yakovlol]

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

[yakovlol]

@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 · Accepted solution]

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.

[yakovlol]

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]

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