Dynamic Same Time Last Year

[RichardC12]

Hi all,

I'm currently trying to create a sales per region dashboard and would like to ask for some advice on my query. I currently have my sales for a selected year as:

YTD PAX = TOTALYTD(SUM(SALES_MOVEMENT[Sales]), 'Calendar'[Date])

I want to be able to see how much sales I had same time last year as well, a caveat to this however is I want to be able to see how many sales are made the prior year before a particular date which is adjusted by a date slider. I have this query:

STLY Passengers =

VAR LATESTDATECURRENTYEAR = MAX(SALES_MOVEMENT[SaleDate])

VAR LATESTDATELASTYEAR = DATE(YEAR(LATESTDATECURRENTYEAR) - 1, MONTH(LATESTDATECURRENTYEAR), DAY(LATESTDATECURRENTYEAR))

VAR FIRSTDATELASTYEAR = DATE(YEAR(LATESTDATECURRENTYEAR) - 1, 1, 1)

VAR TY = SELECTEDVALUE(SALE[SaleYear])

VAR LY = TY - 1

RETURN

CALCULATE(SUM(SALES[Sales]), SALES[SaleYear] = LY, SALES[SaleDate] <= LATESTDATELASTYEAR)

Which works and gives me the values. However, the query is really heavy and takes around 20 seconds to calculate... Is there any way I can get the same results but with a more optimised query? 

Thanks!

[Anonymous]

Hi @RichardC12 ,

Regarding the issue we discussed earlier, I wonder if this has been resolved now? It's been a long time since I've heard back from you and I'm not sure if all is well. Please feel free to let me know if there is anything else I can do to help.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @RichardC12 ,

For your problem, here is the solution I have given which should optimize your DAX query taking too long.

Measure = VAR current1 =MAX('financials'[Date])
VAR late =DATE(YEAR(current1)-1 ,MONTH(current1),DAY(current1)
)
RETURN  CALCULATE(SUM(financials[ Sales]),financials[Date]<=late, YEAR('financials'[Date])=YEAR(late))

I kept your first two self-defined parameters and then calculated your latter part directly using caculate,

in my example data, the speed is getting faster, I hope this helps.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.