Measure building help

DhanashreePrasun 61 Reputation points


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.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,244 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,411 Reputation points

    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.

    1 person found this answer helpful.
    0 comments No comments