topic Re: Actual previous year until the last sales day in DAX Commands and Tips

Actual previous year until the last sales day

Raph — Thu, 09 Mar 2023 11:06:40 GMT

Hello

I have a visual displaying the days of the month (1 march, 2 march, 3 march, without the year) showing the sales of the day of the current year and the sales of the day of the previous year :

2023 2022

1 March 10 8

2 March 12 10

3 March 9

4 March 7

Let's say today is 2 March 2023 and I would like the figures of the previous year showed only until 2 March 2022.

I tried the following code but it keeps showing data until 4 March for the previous year like above :

Actual previous year =

VAR LastSalesDate = CALCULATE(MAX('Sales [Date]),

DATEADD('Sales'[Date],-1,YEAR))

VAR SalesPrevYear = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[day]))

RETURN

CALCULATE(ActuPrevYear,FILTER('Date','Date'[Day]<=LastSalesDate))

Any idea?

Thank you

Raphaël

Re: Actual previous year until the last sales day

tamerj1 — Thu, 09 Mar 2023 12:01:11 GMT

Hi Raph
Please try

Actual previous year = VAR LastSalesDate = CALCULATE ( MAX ( 'Sales'[Date] ), REMOVEFILTERS () ) VAR Dates = FILTER ( PREVIOUSYEAR ( 'Date'[Date] ), FORMAT ( 'Date'[Date], "MMDD" ) <= FORMAT ( LastSalesDate, "MMDD" ) ) RETURN CALCULATE ( [Total Sales], Dates )

Re: Actual previous year until the last sales day

Raph — Thu, 09 Mar 2023 13:34:27 GMT

Hi tamerj1

Thank you very much for you answered which looked smart to me.

I tried it but now I have a big number, always the same, repeating on all the rows of the month... typically like a data modeling problem. The model is really very simple : 2 dim tables (date and stores) linked to 2 fact tables (sales and budget).

This worked well for the budget :

Budget € =

VAR LastSalesDate = CALCULATE(MAX('Sales'[Date]),ALL('Sales))

RETURN

CALCULATE(sum(Budget[Budget]),FILTER('Date','Date'[Day]<=LastSalesDate))

Thank you

Raphaël

Re: Actual previous year until the last sales day

tamerj1 — Thu, 09 Mar 2023 13:41:26 GMT

Raph
Seems I misunderstood the requirement. Please try

Actual previous year = VAR CurrentDate = MAX ( 'Date'[Date] ) VAR Dates = FILTER ( PREVIOUSYEAR ( 'Date'[Date] ), FORMAT ( 'Date'[Date], "MMDD" ) <= FORMAT ( CurrentDate, "MMDD" ) ) RETURN CALCULATE ( [Total Sales], Dates )

Re: Actual previous year until the last sales day

Raph — Thu, 09 Mar 2023 14:06:26 GMT

tamerj1

Thank you. It stops now at the right date but figures are not correct, all much too high (but not all the same).

This returns the good numbers but don't stop at the right date :

Actual previous year =

VAR MaxDatePrevYear = CALCULATE(MAX('Sales conso'[Finalized Date]),SAMEPERIODLASTYEAR('Date'[Jour]))

VAR Dates = FILTER('Date','Date'[Jour]<MaxDatePrevYear)

Var Salespreviousyear = CALCULATE([Total incl VAT],SAMEPERIODLASTYEAR('Date'[Jour]))

RETURN

CALCULATE(Salespreviousyear,Dates)

Thank you

Raphaël

Re: Actual previous year until the last sales day

tamerj1 — Thu, 09 Mar 2023 15:23:57 GMT

Raph

Would you please explain again what exactly the value are you exyto see for each date?

Re: Actual previous year until the last sales day

Raph — Thu, 09 Mar 2023 15:32:24 GMT

tamerj1

Do you know why

CALCULATE(MAX('Date'[Date]),LASTNONBLANK('Sales'[Date],[Sales]))

returns the last day of the year of 2023 and not last day of sales ? It's like the filter is without any effect.

Thank you

Raphaël

Re: Actual previous year until the last sales day

Raph — Thu, 09 Mar 2023 16:02:06 GMT

tamerj1 for each day of the month (1,2,3...) I would like to have the sales of the year and the sales of the previous year. This works simply with the measure

Actual previous year = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Day]))

... but this returns all the values of the month Y-1, and I would like to display the values only until the last day of sales of the current year.

Re: Actual previous year until the last sales day

Raph — Thu, 09 Mar 2023 16:07:27 GMT

tamerj1

Re: Actual previous year until the last sales day

tamerj1 — Fri, 10 Mar 2023 05:17:16 GMT

Hi Raph
Please try

Actual previous year = IF ( MAX ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Sales'[Date] ), ALL ( 'Date' ) ), CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Day] ) ) )

Re: Actual previous year until the last sales day

Raph — Fri, 10 Mar 2023 07:52:07 GMT

Thank you very much, tamerj1 , this is indeed working well except that I lost the sum for the current month (sum is normally displayed if we filter on a a previous month)

Re: Actual previous year until the last sales day

tamerj1 — Fri, 10 Mar 2023 08:18:17 GMT

Raph

you mean total?

Actual previous year =
SUMX (
VALUES ( 'Date'[Day-Month] ),
IF (
CALCULATE ( MAX ( 'Date'[Date] ) )
<= CALCULATE ( MAX ( 'Sales'[Date] ), ALL ( 'Date' ) ),
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Day] ) )
)
)

Re: Actual previous year until the last sales day

Raph — Fri, 10 Mar 2023 09:37:02 GMT

tamerj1

Thank you, yes I meant the Total... I tried with SUMX (first on my own, iterating the days of Date table and then with your suggestion) but the visual won't change...

Re: Actual previous year until the last sales day

tamerj1 — Fri, 10 Mar 2023 12:07:45 GMT

Raph

Have you wrapped MAX ( 'Date'[Date] ) with CALCULATE?

Re: Actual previous year until the last sales day

Raph — Fri, 10 Mar 2023 10:32:11 GMT

tamerj1

Yes, now it's working!

Thank you very much for your support!

Raphaël