Irregular Monthly / Periodic Invoicing

[DJBrennan]

Hi all,

I'm an analyst/consultant working for various clients in the area of procurement. One of our services is to provide the client with reports/dashboards showing usage and/ or charges by supply by month including all the usual time intelligence benefits like last month, last year comparisons etc....

I've begun to realise that I can't easily use standard months for comparison. An easy example is electricity bills. A bill from one supplier for Supply X may start at 01/06 and end on 310/06, a nice neat calendar month. Another supplier for Supply Y might send a bill covering usage from 12/06 to 11/07 or, perhaps a quarterly bill covering anything from 80-95 days (not very exact). I'm looking for some ideas as to how to "normalise" this data to allow reasonable month-on-month reporting. I hope this makes sense.

Many thanks in advance,

Denis

[v-sihou-msft · Accepted solution]

@DJBrennan

In your scenario, since each supplier may have different calendar, I suggest you use Time Intelligence functions like DATESINPERIOD() or DATESBETWEEN() to specify corresponding date range into CALCULATE() with current row start date. 

Regards,