Percent change from previous date

[menerso]

I am trying to create a report in PowerBI that shows the Percentage Change of Crictical data from the previous dates.  The dates are not be consistant for amount of days between each date.

I have a report that looks similar to the table below, but I can't seem to pull up the Critical Previous data so that I can create my Percent Change column/measure.  I was able to create a Previous Date calculated column in my testing... but it didn't help me.

I think I have tried every example of what people have put out there, but I either get nothing showing or an error.

Agency	Date	Critical	Previous Date	Critical Previous	Percent Change
Agency1	9/16/22	44
Agency1	10/6/22	22	9/16/22	44	-50%
Agency1	10/16/22	10	10/6/22	22	-55%
Agency New2	9/16/22	100
Agency New2	10/6/22	75	9/16/22	100	-25%
Agency New2	10/16/22	25	10/6/22	75	-67%

Agency is pulled from another table called Agency Hosts.  It connects to a host field which is not in this report.

Critical is a calculated column

Critical Previous may not be needed, I was just happy I could figure that one out.

Ultimately, I am looking for the Percent Change, but I figure I need the Critical Previous column to get that.

[menerso]

With the data I gave, this worked great.  But went I put it into my actual PowerBI, I end up getting a circular error.  I am assuming it is because of the "Risk Critical" field (which represented the "Critical" field in out test files) is filtering out Critical data in the column.  I have other fields like "Risk High" and "Risk Low" that filters out those in a column.

This is what the Risk Critical field looks like.  

Risk Critical = IF(ISBLANK(CALCULATE(Countrows('Vulnerabilities'),'Vulnerabilities'[OMC Risk]="Critical")),0,CALCULATE(Countrows('Vulnerabilities'),'Vulnerabilities'[OMC Risk]="Critical"))

So, I am stuck on how to use this Risk Critical field in this equation.

Thanks,
Marty

[v-xiaosun-msft]

Hi @menerso ,

Has your problem be solved?

If so, please mark the answers you need as a solution to help the other members find it more quickly.

If not, please tell us in order that we can help you futher more.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@menerso 

is this what you want?

Column = 
var _last=maxx(FILTER('Table','Table'[Agency]=EARLIER('Table'[Agency])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
var _lvalue=maxx(FILTER('Table','Table'[Agency]=EARLIER('Table'[Agency])&&'Table'[Date]=_last),'Table'[Critical])
return if(ISBLANK(_last),blank(),DIVIDE('Table'[Critical]-_lvalue,_lvalue))

[menerso]

Thanks for your response.  That did not work.  It does work if all fields are in one table, but I have the Agency in a different table.

Just so you know, 

Agency column is in a related table which is indexed off of another field.  I am unable to add the 'table'[Agency] field after the EARLIER command.  It just doesn't show up as an option.

The Critical column is a measure.

I tested yours with a related table and I got this...

[ryan_mayu]

@menerso 

if you want to create a measure, you can try this

Measure = 
var _last=maxx(FILTER(all('Table'),'Table'[Agency]=max('Table'[Agency])&&'Table'[Date]<max('Table'[Date])),'Table'[Date])
VAR _lvalue=maxx(FILTER(all('Table'),'Table'[Agency]=max('Table'[Agency])&&'Table'[Date]=_last),'Table'[Critical])
return if(ISBLANK(_last),blank(),DIVIDE(max('Table'[Critical])-_lvalue,_lvalue))

pls see the attachment below

[menerso]

@ryan_mayu  Thanks again for the reply. 

I mentioned that your code DOES work for a single flat table, but does not work when I have two tables that are related.  The file you sent was of a single table.
Do you know how to make this work with two related tables.  I included the test file I have been using here  -  Marty Test.pbix 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu]

@menerso 

pls see the attachment below