Sum of cost of items in licence on any given day

[CharleyK]

Please help - I'm losing my mind.

I have 2 tables:

TABLE 1: Inventory, featuring

• Licence Start Date
• Licence End Date
• #Days in Licence
• Deal Cost per Day

TABLE 2: Simply a calendar date table

They have an active relationship on the Start Date

I want to visualise, as a bar chart, the cost of everything in licence on any given day. 

No amount of measures; relationships and calculated tables has gotten me there in the last 48 hours, and I might just have a breakdown.  What am I not getting here?  SO frustrating being a newb.

Thank you very much in advance!

[CharleyK · Accepted solution]

OMG!  I think I just got it  😄  

Active Costs = sumx(filter(Inventory,[Active Licenses]),Inventory[TotalCostPerDay])

Thank you thank you thank you SO much for your help!!

[v-jingzhang]

@CharleyK 

Glad that you have solved this problem, you may accept the appropriate reply as a solution to close this topic and others will find it quickly. Thanks a lot.

Best Regards,
Community Support Team _ Jing Zhang

[CharleyK]

@mahoneypat     well, this is incredible - I have removed the relationship, and now have a bar chart of number of active licences which gives correct results which is WAY closer than I've gotten so far, so thank you SO much on this, and for the reassurance.  Definitely hadn't reached the passing variables stage yet.... clearly a lot to learn!

You mention changing the calculation to get total cost... which I'm trying, but am failing to get a working version of.  Are you able to enlighten me any further?  I've tried nesting another calculate and making various kinds of other measures etc. Sorry to be a pain, and thank you!!

[aj1973]

Hi,

Create an Inactive relationship from License End Date to your Calendar date table. then you will need to create a DAX measure using CALCULATE  and USERELATIONSHIP formulas to calculate the SUM of the deal cost per day. Add it to your bar shart.

[mahoneypat]

Hang in there.  This is a little more advanced, so don't get too frustrated.  Usually this is done with no relationship between the date and license table, with a measure like this.  Put it in a visual with columns from your date table.  This does the count of active licenses, but you can change the calculation to get total cost, etc.

Active Licenses =
VAR vMaxDate =
    MAX ( 'Date'[Date] )
VAR vMinDate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Inventory ),
        ALLSELECTED ( Inventory[StartDate] ),
        Inventory[StartDate] <= vMaxDate,
        Inventory[EndDate] >= vMinDate
    )

You won't need the ALLSELECTED part if there is no relationship between the tables, but if you do have it for other reasons, that will remove it for this calculation.

Regards,

Pat