Azure Cost Management Data - Getting & Grouping Charges by Latest Tag Value

[BMac-Coop]

I've been working on this on-and-off for weeks and I fear there's an easy answer I'm not seeing.  The source data is the Azure Costing feed.  (pbix here for a sample.  First time giving a PBIX, hopefully it works.)  The data is a (typically) one row per charge table, with details about the resource the charge is against.  These resources could exist for hours, days, or forever.  In some cases the resource (ie, a VM) will live for a few hours then be deleted.  In the data is the list of Tags for the resource at the time of the charge.

What I'd like to get is "what is the latest value of the 'Project Accounting Code' tag for a resource"...to ultimately drive a visualization of cost broken out by that code per month.  I don't care what the code was in the past...I just care what the last/latest one is.  For example, if a VM had a charge on 1/1/2020 that has ProjectAccountingCode:3 and on 2/1/2020 that same VM has ProjectAccountingCode:5 , I want the report for all months to show that all charges for that VM are to go against ProjectAccountingCode:5.

I tried making a seperate call to the Azure Resource Graph to get the current value of the Tags for all resources, but that will only give me current resources...not things that have been deleted.

A note on the sample data: This is not an exact dump from the Azure Costing feed.  This has been modified by me to illustrate a subset of the data to reproduce the challenge.

So based on the data provided in the PBIX, I would expect to see:

• All costs for Server1 go against ProjectAccountingCode 377 (It started as 9, moved to 377, then was removed)
• All costs for Server2 go against ProjectAccountingCode 654 (it started as 9, moved to 377, moved to 654, so charge all to 654)
• All costs for Server3 go against ProjectAccountingCode 123 (all entries have 123)

Said another way, there should be no charges for 9 because the last record for any server was not 9.

Thanks in advance.

[v-yangliu-msft]

Hi  @BMac-Coop ,

Here are the steps you can follow：

1. Create measure.

Measure =
CALCULATE(SUM('Sheet1'[PITProjectAccountingCode]),FILTER(ALL(Sheet1),'Sheet1'[ResourceName]=MAX('Sheet1'[ResourceName])&&
'Sheet1'[BillingPeriodEndDate]=
MAXX( FILTER(ALL(Sheet1),'Sheet1'[ResourceName]=MAX('Sheet1'[ResourceName])),[BillingPeriodEndDate])))

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[BMac-Coop]

Hi.  Thanks for this.  It works in your PBIX file, but I'm having problems implementing it with my real data.  Funny enough, it's doing what I would expect it to do (which shows I'm still struggling to read what it's supposed to be doing).  It's basically adding up all of the PITProjectAccountingCode values and putting the numbers to the Measure:

Any ideas why this would be happening?  I've checked data types against your pbix file and everything looks identical.