Running Total Tweak

[SimonNU]

Hi Guys

I'm about to apply one of the below YTD calculations to about 100 measures. Before I do, I'd like to run it past you geniuses because I'll potentially have all of these measures calculating on 1 worksheet using CUBE formulas. So which of the below two would calculate fastest? Are there any better methods? Is there anything detrimental that I may encounter as a result?

=CALCULATE (
    [Gross Services],
    FILTER (
        ALL ( fct_Query),
        'fct_Query'[PERIOD] <= MAX ( 'fct_Query'[PERIOD] )
    )
)

vs

=CALCULATE (
    [Gross Services],
    FILTER (
        ALL ( fct_Query[B][PERIOD][/B]),
        'fct_Query'[PERIOD] <= MAX ( 'fct_Query'[PERIOD] )
    )
)

vs ... anything better?

 

[scottsen]

There are a few issues here.

1) the 2nd measure won't give you want you want if you have anything on filters (rows, columns, slicers) that are from the fct_Query table other than Period. Year, Month, Day, whatever.
2) How is this YEAR to date? It looks like LIFETIME to date to me? What constrains it to a year?
3) You apparently don't have a Calendar table. You want one. It will be less rows than your fact table, and you can filter on THAT instead, for better speed.
4) If you have a traditional calendar, and a calender table, you can just use DATEYTD() instead of FILTER.

Which... sounded harsh, but is only intended to be helpful

 

[SimonNU]

Thanks Scott, I'll take it on-board.

The below interests me:
"It will be less rows than your fact table, and you can filter on THAT instead, for better speed."
Are you saying that introducing another table and referencing that in my measures would make it faster as opposed to referencing only 1 table?

 

[scottsen]

Yep. That's what I'm saying

Imagine you have Calendar table with 1000 rows (about 3 years). And a fact table by Date Sold with 50 million rows. Filtering the Calendar table for Dates in April 2014... will be WAY faster than trying to apply that filtering directly to the huge Fact table.

I should really "do that", and blog the results. But just trust me