Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Percent change from previous date

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.

 

AgencyDateCriticalPrevious DateCritical PreviousPercent Change
Agency19/16/2244   
Agency110/6/22229/16/2244-50%
Agency110/16/221010/6/2222-55%
Agency New29/16/22100   
Agency New210/6/22759/16/22100-25%
Agency New210/16/222510/6/2275-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.

 

Need Help

8 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    Anonymous 

    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))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        ryan_mayu
        Super User
        3 years ago

        Anonymous 

        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

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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