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
davidqsuires
Frequent Visitor

DAX Formula Comparison

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 ! 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@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

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@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

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.