cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Fcoatis Member

## % Change in a non contiguous Matrix

I have this matrix with measure Tot Gross = sum(INVEST[Gross Bal]) . How do I calculate the percentual difference of Tot Gross (being calendar non contiguous due to holidays and weekends)? 1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor

## Re: % Change in a non contiguous Matrix

Hi @Fcoatis,

You can use a measure, please review the following solution.

I have sample data table. Add index column in Edit Query-> Close & Apply. You will get the following table. Create a calculated column using the formula.

`Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)` Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.

`Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])` If you have other issues, please let me know.

Best Regards,
Angelia

4 REPLIES 4
v-huizhn-msft Super Contributor

## Re: % Change in a non contiguous Matrix

Hi @Fcoatis,

What's the mean of the percentual difference of Tot Gross, could you please post an example?

The difference includes increase and decrease between the two numbers you are comparing.

Increase = (New Number - Original Number)÷ Original Number × 100.

Decrease = (Original Number - New Number)÷ Original Number × 100.

In your requirement, what's the New Number and Original Number?

Best Regards,
Angelia

Fcoatis Member

## Re: % Change in a non contiguous Matrix

Hi Angelina,

I'm looking for a measure that calculates (actual value/previous value)-1 v-huizhn-msft Super Contributor

## Re: % Change in a non contiguous Matrix

Hi @Fcoatis,

You can use a measure, please review the following solution.

I have sample data table. Add index column in Edit Query-> Close & Apply. You will get the following table. Create a calculated column using the formula.

`Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)` Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.

`Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])` If you have other issues, please let me know.

Best Regards,
Angelia

Fcoatis Member

## Re: % Change in a non contiguous Matrix

Thank you Angelia,

I think this is the solution. I'll have to adapt to my model in order to have the right sequential index.

Best regards

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 6 members 2,813 guests
Recent signins:
• roofi00 • prashantnegi • jonasheise • Gordonlilj • Narayana3635 • ccapowerbi Please welcome our newest community members:
• despo • DMuturi • Karthikshan • ElizabethFoy • jeppoo1 • simonf3 • solenttest1 