Running Totals Take Forever Need Some Direction

[JLEmlet]

Howdy -

I have what I think is a rather simple problem.  I have a table with about 800K rows in it.  There's a date column, an hours column, a customer column and a sales column.  I'd like for the report to allow the users to select a date and a customer and have a table show the total sales by hour as well as cume sales by hour.  I have a working solution, but it literally takes about 60 seconds for the table to populate when I change dates or customers.  So I must be doing something wrong.  My running total formula is 

Sales RT =

CALCULATE(

    SUM('GA'[Sales]),

    FILTER(

        ALLSELECTED('GA'[CalendarDateTime]),

        ISONORAFTER('GA'[CalendarDateTime], MAX('GA'[CalendarDateTime]), DESC)

    )

)

If you can help me solve this issue, my next challenge is showing the total sales for the prior year for the same client/day selected.

[parry2k]

Just curious why we are not advising to add the calendar dimension rather fixing the measure. Am I missing something?

[smpa01]

@JLEmlet  does this improve?

Sales RT =
VAR _tbl =
    ALLSELECTED ( 'GA'[CalendarDateTime] )
VAR _max =
    MAX ( 'GA'[CalendarDateTime] )
RETURN
    CALCULATE (
        SUM ( 'GA'[Sales] ),
        FILTER ( _tbl, ISONORAFTER ( 'Table'[Date], _max, DESC ) )
    )

[parry2k · Accepted solution]

@JLEmlet fundamentally you are missing a calendar dimension in your model, any time intelligence-based calculations should be done using Date/Calendar dimension in the model and it is a best practice. Add a calendar dimension, you can follow my blog post to add one and in your measure, replace all the references to the calendar dimension and the column from this dimension and it should be lightning fast.

Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

You can always find a workaround but I would hate to advise those and highly recommend adding a Calendar dimension in your model.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Greg_Deckler]

@JLEmlet Well, maybe try this equivalent:

Sales RT =
  VAR __MaxDate = MAX('GA'[CalendarDateTime])
  VAR __Table = FILTER('GA', 'GA'[CalendarDateTime] <= __MaxDate)
RETURN
  SUMX(__Table,[Sales])