Sales: Previous Year's Quarter

[Oros]

Hello.

This measure works to get the sales of last year's fiscal year by month.

SALES (LY) = CALCULATE(SUM('SALES TABLE'[SALES]),DATESMTD(ENDOFMONTH(dateadd('Calendar'[Date],-12,MONTH))))

What would be the correct measure to get the PREVIOUS YEAR's QUARTER sales?  I have a date (calendar table).

Thanks.

[helpxel]

Hey! 

Use this :

Prior Year Quarter Sales = 
VAR CurrentDate = TODAY()
VAR CurrentQ = QUARTER( CurrentDate )
VAR PriorYear = YEAR ( CurrentDate ) - 1
VAR Result =
CALCULATE ( 
    CALCULATE ( SUM ( ‘SALES TABLE’[SALES] ) ),
    FILTER (
        ALL ( ‘Calendar’ ),
        AND (
            QUARTER ( ‘Calendar’[Date] ) = CurrentQ,
            YEAR ( ‘Calendar’[Date] ) = PriorYear
        )
    )
)
RETURN 
Result

The measure above wil work perfectly however it would be best practice to seperate out your Sum of Sales measure into a seperate measure and to add a quarter column to your date table.

SQLBI has great resources on creating date tables 🙂

----------------------------------------------------------------------------------------------------------------------------------

Really appreciate your Kudos!

Easy Power BI Tutorials

Unleash the full potential of Power BI with Help Xel

Visit HelpXel

[Oros]

Hi @helpxel 

Thank you very much for quick reply.  The numbers from last year's Q does not match.  But it seems that it just needs a minor adjustment.  Our fiscal year starts in November. 

What would be the correct way to adjust the measure that you suggested based on our fiscal year?  

Q1=Nov. to Jan.

Q2=Feb. to Apr.

Q3=May to Jun.

Q4=July to Oct.

Here is my current Calendar Table

[helpxel · Accepted solution]

Hey no problem! @Oros 

Really happy to see such a good use of your date table!

All you need to do is change which quarter column we are working with and how we calculate it ( thanks to your great use of the date table )!

Providing your fiscal quarter column is text and in the following format "Q1" then use this 😊

Prior Year Quarter Sales = 
VAR CurrentDate = TODAY()
VAR CurrentQ = "Q" & IF ( MONTH ( CurrentDate ) >= 10, 1, QUARTER ( EOMONTH ( CurrentDate, 2 ) ) )
VAR PriorYear = IF ( MONTH ( CurrentDate ) >= 10, YEAR ( CurrentDate ) , YEAR ( CurrentDate ) - 1 )
VAR Result =
CALCULATE ( 
    CALCULATE ( SUM ( ‘SALES TABLE’[SALES] ) ),
    FILTER (
        ALL ( ‘Calendar’ ),
        AND (
            QUARTER ( ‘Calendar’[Fiscal Qtr] ) = CurrentQ,
            YEAR ( ‘Calendar’[Fiscal Year] ) = PriorYear
        )
    )
)
RETURN 
Result

Really appreciate your Kudos!

Easy Power BI Tutorials

Unleash the full potential of Power BI with Help Xel

Visit HelpXel

[Oros]

Thanks @helpxel ,

It works!