DAX Formula Comparison

[davidqsuires]

After recently building out our first SSAS Tabular data model, we ended up with 2 measures/methods to get my company's Fiscal Year to Date Sales. I did not build the initial formulas and i'm new to DAX so I could use a little help. 

One method is performing much better when test drilling through our cube in Excel. I am trying to get the slower method to work as fast, as the faster method. If that makes sense. 

The slow method requires picking a date out of our date table, then pulling in this measure. It works but it's slow. Is there anything obvious we could tweak on this to make it work faster?

Fisc YTD Sales :=
IF (
    HASONEVALUE ( 'Invoice Date'[Fiscal Year] ) && MAX ( 'Invoice Date'[Date] ),
    CALCULATE (
        [Sales],
        ALL ( 'Invoice Date' ),
        FILTER (
            ALL ( 'Invoice Date' ),
            'Invoice Date'[Fiscal Year] = VALUES ( 'Invoice Date'[Fiscal Year] )
                && 'Invoice Date'[Date] <= MAX ( 'Invoice Date'[Date] )
        )
    ),
    BLANK ()

The faster method requires using a "Relative Fiscal Year Offset" field in our date table. 0 would be the current fiscal year. 

We then just drag in our "Sales" measure which is simply: Sales:=CALCULATE(SUM([LineAmount])). We use the Sales measure for any dynamic date range of sales but the Fiscal YTD Sales measure is always the current YTD sales for the date selected. 

Thanks for any help ! 

[v-sihou-msft · Accepted solution]

@davidqsuires

In DAX, to calculate YTD for fiscal years, you can directly use TOTALYTD(), and specify a year_end_date argument in this function. For example:

=TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey], ALL(‘DateTime’), “6/30”)

It should perform much faster than your above formula. For more details, please see: TOTALYTD()

Regards,

Simon Hou