Substruct Week vs. Previous Week in Matrix Column

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.