Calculate units consumed and remaining during billing cycle.

[bob57]

These two tables make up the data model (there is also a Dates table).
Orders

Units Consumed

The billing period for ABC is from the 23rd through the 22nd. The billing period for XYZ is from the 4th through the 3rd. I need to create a table with the following requirements:
Column 1: Billing date
Column 2: Company
Column 3: Units Consumed During Billing Period
Column 4: Remaining Units (Not Yet Consumed)
The "Billing Date" column displays up to the next billing date (but not beyond). Columns 3 and 4 should display the changes as units are consumed during the billing cycle when the report is refreshed. The table would resemble this:

Thank you for your time, effort, and knowledge.

Bob

[Anonymous]

You can use this as a springboard to other calculations:

Billing Dates = // calculated table
SELECTCOLUMNS(
    GENERATE(
        Company,
        
        var __firstBillDate = Company[First Bill Date]
        var __lastBillDate = Company[Last Bill Date]
        var __monthCount =
            DATEDIFF(
                __firstBillDate,
                __lastBillDate,
                MONTH
            )
        return
            ADDCOLUMNS(
                GENERATESERIES(0, __monthCount, 1),
                "@BillingDate",
                    DATEADD(
                        FILTER(
                            DISTINCT( Dates[Date] ),
                            Dates[Date] = __firstBillDate
                        ),
                        [Value],
                        MONTH
                    ),
                // This is only a technical column
                // so that it's easy to chop the
                // Dates table into billing periods.
                "@PrevBillingDate",
                    if( [Value] = 0,
                        MIN( Dates[Date] ),
                        DATEADD(
                            FILTER(
                                DISTINCT( Dates[Date] ),
                                Dates[Date] = __firstBillDate
                            ),
                            [Value] - 1,
                            MONTH
                        )
                    )
            )
    ),
    
    "Company", Company[Company],
    "Billing Date", [@BillingDate],
    "Prev Billing Date", [@PrevBillingDate]
)

// Now join Company on [Company] to the table
// and join Dates on [Date] = [Billing Date].
// Hide the above table.

[Units Consumed During Billing Period] =
SUMX(
    // We have to sum up the units consumed
    // for all the visible companies in the
    // selected period of time. Note that
    // this measure is NOT the real units 
    // consumed during the selected period.
    // Please read this code and understand
    // what it does. Thanks.
    SUMMARIZE(
    	'Billing Dates',
    	Company[Company],
    	'Billing Dates'[Billing Date],
    	'Billing Dates'[Prev Billing Date]
    ),
    
    var __prevBillingDate =
    	'Billing Dates'[Prev Billing Date]
    var __billingDate =
    	'Billing Dates'[Billing Date]
    return
        CALCULATE(
            SUM( 'Units Consumed'[Units] ),
            Dates[Date] > __prevBillingDate,
            Dates[Date] <= __billingDate
        )
)

// Now just drop the dates from Dates
// and companies from Company onto
// your table and add the measure.

[Anonymous]

By the way, for the above to work, Dates must be a date table marked as such in the model. Otherwise, you'll have to remove all filters from Dates under the last CALCULATE in the measure.

[Anonymous]

OK. I've checked it with a model. IT WORKS CORRECTLY. So there you have it - a full solution.

[bob57]

Thank you Daxer. I tried to implement this solution and it is close to working for me. I got pulled away and wasn't able to debug. I will follow up in a few days when I'm back on it.

[amitchandak]

@bob57 , I would have preferred data in table format to try.

Create a date table and get measure like this and subtract them

Current Unit = CALCULATE(Maxx(FILTER(Table,Table[Start Date]<=max('Date'[Date]) && ( Table[End Date]>max('Date'[Date]))),(Table[Unit sold])))
Cumm = CALCULATE(SUM(Unit[Unit]),filter(date,date[date] <=maxx(date,date[date])))

//or

Cumm = CALCULATE(SUM(Unit[Unit]),filter(date,date[date] <=maxx(Unit,Unit[date])))

refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[bob57]

Thank you for taking the time to reply. I was pulled away from this project and am just getting beck to this now. I will give your suggestions serious consideration.

[Greg_Deckler]

@bob57 - I think that the first thing you will need is a table that contains all of the your customers' billing dates by month. I'm going to try to figure out how you might get that, I have a similar situation in Open Tickets but this is more complex. How many customers are we talking?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[bob57]

Greg,

This is a new product, so right now we have only a couple of customers. We anticipate 30 - 40 at some point. In addition, after the "Last Bill Date" the contract is automatically terminated, so that customer would be removed from the table, unless the customer renews, which would result in new First and Last Bill Dates.

Thank you,

Bob