Measure building help

Question

Hello,

I am fairly new at DAX and I need help in building a formula in Tabular analysis services.

I have a table with the following fields:

Amount PeriodFrom PeriodTo BilledSeconds FromBilledSeconds ToBilledSeconds
100.00 2021-01-12 12:36:20.000 2021-01-22 13:12:30.000 866170 41020 47550

Billed Seconds is the difference in seconds between the Period From and Period To.
From billed seconds is the number of seconds billed for the day PeriodFrom.
ToBilledSeconds is the number of seconds billed for the PeriodTo.

Now, if from the Date table, I want to calculate the amount 100 being split by using the number of seconds.
Like on 12th Jan, how much was the bill value for 41020 seconds.

Similarly, the user can select any date and if that date falls between the from and to dates, the amount 100 should be split by calculating the usage for 86400 seconds.
I calculated all the seconds in the database and brought them as fields from the SQL view.
I am not sure if this is the right approach.

Appreciate if someone can help me solve the problem.

Thanks in advance.

Answer 1

I calculated all the seconds in the database and brought them as fields from the SQL view.

For this analysis, transform your table into two fields, Date and Split Amount, that is, pre-calculate the splits. Set up regular relationship with the Date table. Your measure will then be SUM ( 'Table'[Split Amount] ), nice and easy.
The logic is universal: start with the data model, remove as much complexity as possible, pre-calculate all you can.