How to get last year same day revenue using DAX

[Rosh89]

Hi,

I was using this DAX to calculate last year same day revenue:

LY daily total revenue = CALCULATE(Total[Any day Total revenue], SAMEPERIODLASTYEAR('Date'[Date]))

(Any day Total revenue - calculates the revenue for present day)

This DAX works perfectly fine with a date slicer in place. But I had to remove the date slicer because I have to send email reports daily with scheduled refresh in service and it does not work with a date slicer in place. The above DAX does not work without a date slicer. I wrote the following DAX to get it working without the date slicer:

LY daily total revenue = CALCULATE(Total[Any day Total revenue], FILTER(ALL('Date'), 'Date'[Date]=MAX(Date[Date])),SAMEPERIODLASTYEAR(('Date'[Date])))

I am not sure how to correct this. Can anyone help me out with this?

Thanks in advance!

Best Regards

Rosh

 

[Whitewater100]

Hi:

If you have a date table you can bring in that column and place your [Total Sales] measure (whatever measure you are using like  Total Sales = SUM[SalesTable]) in the values section. 

To obtain last year for Total Sales 

 LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])).  or

                  CALCULATE([Total Sales], DATEADD(Dates[Date], -1, YEAR))

Your visual will break down the sales based on date, week,month, etc...based on the columns you choose from your date table.

[Rosh89]

Hi @Whitewater100 ,

I am looking for last year same day sales and not the total sales. For example if today is 16/3/2022 then I want to compare the sales of this date against last year 16/3/2021. I used this DAX format " LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))." to get the last year MTD and last year YTD sales and it worked perfectly even without a date slicer. 

But for last year same day it doesnt work and considers all the dates in my data. I tried the second DAX option you suggested but that also doesnt work.

Best Regards

Rosh

[aj1973]

Hi @Rosh89 

Use this,

LY daily total revenue = CALCULATE(Total[Any day Total revenue], FILTER(ALL('Date'), 'Date'[Date]=MAX('Date'[Date])),SAMEPERIODLASTYEAR(('Date'[Date])))

Were missing

[Rosh89]

Hi @aj1973 

I tried what you suggested but now the visuals go blank. I dint get any error with the DAX though.

Best Regards,

Rosh

[PaulDBrown]

How is the visual set up? You need a date(s) to be able to calculate the same value for the same date in the previous year. If there isn't a date, you can't compute the value.

[Rosh89]

Hi @PaulDBrown 

Do you mean a date slicer in the report? I do not want to have a date slicer in the report. I was able to get the last year MTD and YTD sales without a date slicer so I am not sure why it doesnt work for last year same day.

Best Regards

Rosh

[PaulDBrown]

No, I mean the visual must have a date. Can you show a depiction of the visual?

[Rosh89]

Hi @PaulDBrown 

So this visual(KPI) shows LY same day  correctly. 

This is a visual in the table using the same measure LY same day and it shows a different number. It should also show € 165.957 like the above image.

Best Regards

Rosh

[PaulDBrown · Accepted solution]

The KPI visual selects and displays the values for the last date in the trend axis by default. This is providing the context for the LY

 In the table visual, there is no date context, so the value displayed is not related to any date. 

You can try to solve this by creating a measure to use in the filter pane to filter the date. Something along the lines of:

Max Date =
VAR MXD =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
RETURN
    IF ( MAX ( 'Table'[Date] ) = MXD, 1 )

Add the relevant date filed (the one you have refered to in the measure) as a filter for the visual, select the option for TopN and set the value to 1:

or if the dates are upto today, for example, you can set the filter directly using the relative date option for the date field (no need for a measure)

[Rosh89]

Hi @PaulDBrown 

It worked!! Many thanks you are a savior! 🙂

Best Regards

Rosh