Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamic Same Time Last Year

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!



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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.