Forum Discussion
Irregular Monthly / Periodic Invoicing
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
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,
1 Reply
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,