Forum Discussion

JLEmlet's avatar
JLEmlet
Regular Visitor
4 years ago
Solved

Running Totals Take Forever Need Some Direction

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.

 

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    JLEmlet Well, maybe try this equivalent:

    Sales RT =
  VAR __MaxDate = MAX('GA'[CalendarDateTime])
  VAR __Table = FILTER('GA', 'GA'[CalendarDateTime] <= __MaxDate)
RETURN
  SUMX(__Table,[Sales])
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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 Solutions

     

    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.⚡

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

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