Forum Discussion
Calculate Fees Due from Duration Range without Calendar Table Relationship
- 8 years ago
Anonymous- Thanks to Phil_Seamark's new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I've been able to come up with a general solution to this type of problem. Take a look at the Quick Measure "Periodic Billing" that I posted to the Quick Measures Gallery: https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365#M148
I'm pretty sure it will do what you want.
Here is what I would do. Use GENERATE to create a new calculated table. You can then link that table to your calendar table.
Table = SELECTCOLUMNS( FILTER( GENERATE(
'YourTable',
'CalendarTable'
),
[Date] >= [from_date] &&
[Date] < [billed_upto]
),
"ID", [member_id],
"Service", [service_name],
"Amount",[amount],
"Date", [Date]
)
Something like that. At that point the problem should become trivial. Now the above assumes daily billing I suppose and you probably need monthly billing but that is the technique that I would use. You might just need a "calendar" table that was a list of months or month/years. If you could post some sample data in a format that can be copied and pasted easily, might be able to come up with a more specific solution.
Anonymous- Thanks to Phil_Seamark's new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I've been able to come up with a general solution to this type of problem. Take a look at the Quick Measure "Periodic Billing" that I posted to the Quick Measures Gallery: https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365#M148
I'm pretty sure it will do what you want.
- Anonymous8 years agoNot applicable
This looks amazing Greg_Deckler; really do appreciate your time and help.
I'll have a chance to use this and try and work something out specifc to my report tomorrow. I may post back with some sample scrubbed data depending on how much I struggle (still really new to DAX).
Thank you again.