DAX on Quarters

[louie_c]

Hi, Inquring on how i can get this following data given quarterly date filters. The catch is:

* need to get comparative number of months only when quarter is incomplete.

Actual Quarter vs. Last Quarter and Target

Actual is say, still as of Jan'22 only.

Target is fully given for entire year already.

So when we select Q1 2022

• Actual should be = Jan'22 (no problem here)
• Last Quarter should be = Oct'21 only, not summing Oct'21 to Dec'21 since only 1 month data is available for current quarter
• Target should be = Jan'22, should not include Feb'22 to Mar'22 even data is available since only 1 month data is available for current quarter

Also if we can get lifetime to date based on the selected quarter:

Example: Start Date of data is Oct'2017 latest is Jan'2022

When I select:

Q3 2021 Lifetime should be Oct'2017 to Sep'2021 sum

Q4 2021 Lifetime should be Oct'2017 to Dec'2021 sum

Q1 2022 Lifetime should be Oct'2017 to Jan'2022 sum

See Illustration.

Thanks in advance!

[Whitewater100 · Accepted solution]

Hello:

You can do a couple of steps:

Create a field in Date Table to show if Qtr Complete:

Completed Qtr =

VAR ThisQtrID =

LOOKUPVALUE(Dates[QuarternYear], Dates[Date], TODAY())

RETURN Dates[QuarternYear] < ThisQtrID

Then first measure:

Prev Qtr = CALCULATE([Total Sales], PREVIOUSQUARTER(Dates[Date]))

Then last measure:

Previous Qtr =

var PQ = [Prev Qtr]

var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)

return

IF(QtrComplete = 1,

PQ,

CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]), TODAY())

)

)

[Whitewater100 · Accepted solution]

Hello:

You can do a couple of steps:

Create a field in Date Table to show if Qtr Complete:

Completed Qtr =

VAR ThisQtrID =

LOOKUPVALUE(Dates[QuarternYear], Dates[Date], TODAY())

RETURN Dates[QuarternYear] < ThisQtrID

Then first measure:

Prev Qtr = CALCULATE([Total Sales], PREVIOUSQUARTER(Dates[Date]))

Then last measure:

Previous Qtr =

var PQ = [Prev Qtr]

var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)

return

IF(QtrComplete = 1,

PQ,

CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]), TODAY())

)

)

[louie_c]

Thanks. Will check this solution.

[Whitewater100 · Accepted solution]

If you want thru EOM prev month vs TODAY on last measure suggested you can use:

Previous Qtr =

var PQ = [Prev Qtr]

var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)

return

IF(QtrComplete = 1,

PQ,

CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]),LASTDATE(PREVIOUSMONTH(Dates[Date])

)

)))

[amitchandak]

@louie_c, last three like cumulative

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

only when qtr is selected

Cumm Sales till last qtr = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=eomonth(max('Date'[date]),-3) ))