Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
4 years ago
Solved

Exclude previous year from dateadd calculation

Hi everyone, I have this dax that calculates the previous quarter's revenue. With the quarter slicer selected, I wanted to start calculating the quarter from year 22 which means I wanted to EXCLUDE the previous/past years and start having this measure by 2022. 

Say, if I selected Q1 on the slicer, it won't calculate anything as it will going to calculate the previous year. Thanks in advance 🙂

Previous Quarter Revenue = 
    CALCULATE ([Revenue],
        DATEADD ( Dates[Date], -1, QUARTER )
    )

 

Need Help
Show and Tell
Tips and Tricks
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    Hi ronaldbalza2023 

     

    I don't think your formula calculates prior quarter revenue...I think it's going to calculate revenue for the date one quarter earlier.

     

    For how to exclude prior year dates from your prior quarter calculaton try this

    VAR	IncludedDates =
	EXCEPT(
		PREVIOUSQUARTER(DateTable[Date]),
		PREVIOUSYEAR(DateTable[Date])
	)
RETURN

CALCULATE(
	[Revenue],
	IncludedDates
)

    I typed it in Notepad so may/may not be syntactically correct, but should get you where you want to go.

     

    Hope this helps!

2 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    Hi ronaldbalza2023 

     

    I don't think your formula calculates prior quarter revenue...I think it's going to calculate revenue for the date one quarter earlier.

     

    For how to exclude prior year dates from your prior quarter calculaton try this

    VAR	IncludedDates =
	EXCEPT(
		PREVIOUSQUARTER(DateTable[Date]),
		PREVIOUSYEAR(DateTable[Date])
	)
RETURN

CALCULATE(
	[Revenue],
	IncludedDates
)

    I typed it in Notepad so may/may not be syntactically correct, but should get you where you want to go.

     

    Hope this helps!