PowerBI Pivoting One Time Purchase to Match Overall Usage Charges

[prevmpeterson]

Hi,

I'm trying to do something it seems like others would have encountered.  We are using Azure Consumption Insights to get billing data from Azure.  There are two different tables being returned that I'm trying to figure out how to report on together.  Usage which is a daily aggregation of all datacenter expense (Compute, Storage, etc.).  So lots and lots of rows.  And then RICharges, which is a reflection of a 1 time purchase of a resource for a period of time.  So in our case, we have purchased Reserved Instances for Compute.  This means there is one row for that period of time.

Because I need to show costs in Azure, I want to be able to still account for the Reserved Instances Charges daily along with the regular usage costs so that we don't lose the fact that we are still paying for those machines (just a lower cost hopefully).  And so I want to take anything that is a "P1Y" (Term) where P1Y = 1 year, P3Y would be 3, and started on (Event Date) "07/04/2019 7:55:25 PM" and had a cost (Amount) of 1000.00 and spread it out over 365 days, if P1Y and 1085 if P3Y.

[drwho]

I had a similiar issue. I solved it by adding below column to the usagedetails table. Please note that Microsofts latest change (https://docs.microsoft.com/da-dk/azure/billing/billing-monthly-payments-reservations) permits up front as a monthly payments. The richarges table does (currently) not hold the billing terms. Consequently, the suggestion below does not handle this scenario (assumes up front payment):

RI_charge = IF([meterSubCategory] = "Reservation-base VM";

IF(LOOKUPVALUE(RIUsage[ReservationOrderId];RIUsage[UsageDate];[Date].[Date];RIUsage[InstanceId];[InstanceId];-2)<>-2;

(

(

LOOKUPVALUE(RICharges[Amount];RICharges[ReservationOrderId];

LOOKUPVALUE(RIUsage[ReservationOrderId];RIUsage[UsageDate];[Date].[Date];RIUsage[InstanceId];[InstanceId]))

) /

(

24 * DATEDIFF(EOMONTH([Date];(-1)); EOMONTH([Date];0);DAY) *

IF(LOOKUPVALUE(RICharges[Term];RICharges[ReservationOrderId];

LOOKUPVALUE(RIUsage[ReservationOrderId];RIUsage[UsageDate];[Date].[Date];RIUsage[InstanceId];[InstanceId]))="P1Y";

12;

36) *

LOOKUPVALUE(RICharges[Quantity];RICharges[ReservationOrderId];

LOOKUPVALUE(RIUsage[ReservationOrderId];RIUsage[UsageDate];[Date].[Date];RIUsage[InstanceId];[InstanceId]))

)

) *

LOOKUPVALUE(RIUsage[UsedHours];RIUsage[UsageDate];[Date].[Date];RIUsage[InstanceId];[InstanceId])

;

0);

0

)

[dax]

Hi prevmpeterson, 

I can't reproduce your design based on your description, so if possible, could you please inform me more detailed information(such as your sample data and your expecting output)? Then I will hele you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi