how to calculate total daily rental

[wotkara]

Hi, I am needing to show total daily charge. I have manually calculated to show the results I am needing - on Jan 1, our total rental items billed = $400 but on Jan 4, it was only $100. What is the best way to calculate daily total? Appreciate any help. Thank you in advance.

[Anonymous]

hi @wotkara 

I have used below tables

Inv

Chg

Calendar table

Calendar =

VAR _minDate = MIN(Inv[START])

VAR _maxDate = MAX(Inv[STOP])

RETURN CALENDAR( _minDate, _maxDate)

Datamodel

Measure

-----------------

Total Charge =

VAR _SelDate = SELECTEDVALUE('Calendar'[Date])

RETURN

SUMX( Inv,

IF(

Inv[START] <= _SelDate && Inv[STOP] >= _SelDate,

RELATED(Chg[CHARGE]),

0

)

)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

total charge measure: = 
VAR _t =
    ADDCOLUMNS ( data, "@rentdays", DATEDIFF ( data[start], data[stop], DAY ) + 1 )
VAR _daiylycharge =
    ADDCOLUMNS ( _t, "@dailycharge", DIVIDE ( data[total charge], [@rentdays] ) )
VAR _dailytable =
    ADDCOLUMNS (
        'calendar',
        "@dailycharge",
            SUMX (
                FILTER (
                    _daiylycharge,
                    data[start] <= EARLIER ( 'calendar'[Date] )
                        && data[stop] >= EARLIER ( 'calendar'[Date] )
                ),
                [@dailycharge]
            )
    )
RETURN
    SUMX ( _dailytable, [@dailycharge] )

[wotkara]

Thank you so much! This worked. I greatly appreciate your help 🙂