topic Re: how to calculate total daily rental in DAX Commands and Tips

how to calculate total daily rental

wotkara — Wed, 24 Apr 2024 03:22:14 GMT

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.

Re: how to calculate total daily rental

Jihwan_Kim — Wed, 24 Apr 2024 04:37:42 GMT

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] )

Re: how to calculate total daily rental

Anonymous — Wed, 24 Apr 2024 05:02:05 GMT

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]),

)

Re: how to calculate total daily rental

wotkara — Thu, 25 Apr 2024 22:14:09 GMT

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