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
Raph
Helper III
Helper III

Actual previous year until the last sales day

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

1 ACCEPTED SOLUTION

@Raph

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

View solution in original post

14 REPLIES 14
Raph
Helper III
Helper III

@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

@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

@Raph 

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

@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.

@tamerj1 

Raph_0-1678377993650.png

 

Hi @Raph 
Please try

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

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)

Raph_0-1678434689733.png

 

@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] ) )
)
)

@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...

@Raph

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

@tamerj1 

Yes, now it's working!

Thank you very much for your support!

 

Raphaël

Raph
Helper III
Helper III

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

@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 )
tamerj1
Super User
Super User

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 )

 

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.

Top Solution Authors