Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Running Total by Hotel id

Dear Friends

I have table: hotelid, date  & amount and need to calculate run Total amount :

jia0077_0-1670599868450.png

 

 

And need to get result in this format:

When no slicer output will be:

jia0077_1-1670599910574.png

 

But I am getting correct result when I slice hotel id but when I don’t slice hotelId getting wrong data on 4/6 December

jia0077_0-1670599513639.png

 

jia0077_1-1670599513641.png

jia0077_4-1670599564767.png

 

 

 

 

112 selected:

jia0077_2-1670599513641.png

 

113 selected:

jia0077_3-1670599513642.png

 

I created the measures:

runTotal = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]<=MAX('Table'[date]))

,VALUES('Table'[hotelid])

)

 

Could you please anyone help me

 

2 ACCEPTED SOLUTIONS

Hi,

thank you for your message.

Please try the below, or if it does not suit your requirement, please share your sample pbix file.

 

runTotal by date =
CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[date] <= MAX ( 'Calendar'[date] ) )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

Hi,

Thank you for your feedback.

I am not sure but please also try the below after assigning the calendar table as Date table.

If it does not provide the correct answer, then please share your sample pbix file, and then I can try to look into it to come up with a more accurate solution.

 

runTotal by date =
CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Calendar'[date] ), 'Calendar'[date] <= MAX ( 'Calendar'[date] ) )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table and then create a relationship (Many to One and Single) from the Date column of your Table to the Date column of the Calendar Table.  Create your slicer from the Date column of the Calendar Table.  To your visual, drg Date from the Calendar Table.  Write these measures:

Total = sum(Table[Amount])

Running total = calculate([Total],datesbetween(Calendar[Date],minx(allselected(Calendar[Date]),calendar[Date]),max(calendar[date])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

 

Jihwan_Kim_0-1670601254484.png

 

 

runTotal =
CALCULATE ( SUM ( 'Table'[amount] ), 'Table'[date] <= MAX ( 'Table'[date] ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

give wrong info when i slice hote id

jia0077_0-1670601759336.png

new measures
runTotal by date = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]<=MAX('Table'[date]))

)

 

runTotal by date =
CALCULATE (
SUM ( 'Table'[amount] ),
FILTER ( ALL ( 'Table'[date] ), 'Table'[date] <= MAX ( 'Table'[date] ) )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

It's work fine, but table is related with Dates table and I have slicer from Dates table . How can I apply Dates(date) to above dax

Hi,

thank you for your message.

Please try the below, or if it does not suit your requirement, please share your sample pbix file.

 

runTotal by date =
CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[date] <= MAX ( 'Calendar'[date] ) )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Thanks, its giving the correct information but its taking more time than usual.

Hi,

Thank you for your feedback.

I am not sure but please also try the below after assigning the calendar table as Date table.

If it does not provide the correct answer, then please share your sample pbix file, and then I can try to look into it to come up with a more accurate solution.

 

runTotal by date =
CALCULATE (
    SUM ( 'Table'[amount] ),
    FILTER ( ALL ( 'Calendar'[date] ), 'Calendar'[date] <= MAX ( 'Calendar'[date] ) )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.