topic Re: How to use PREVIOUSQUARTER in DAX Commands and Tips

How to use PREVIOUSQUARTER

VoltesDev — Tue, 12 Apr 2022 05:34:14 GMT

Hi guys,

When I have this kind of data :

I want to have a measure to current quarter (CQ) and Previous Quarter (PQ), what DAX I actually have to use ?

I have a Date table that relates to that column "Actual Closed Date" and also I have Page Filter context of this Date date table to be relative 1 year.

I I tried to count without any date filter in my DAX (Page filter still effected), like this :

Win Count = CALCULATE( DISTINCTCOUNT(Opportunities[opportunityid]), Opportunities,Opportunities[statecodename] = "Won" )

I have figures like the picture.

I created this measure for CQ :

Win Count CQ = CALCULATE( DISTINCTCOUNT(Opportunities[opportunityid]), Opportunities, Opportunities[statecodename] = "Won", DATESQTD('Date Table'[Date]) )

And I'm using PreviousQuarter like this :

Win Count PQ = CALCULATE( DISTINCTCOUNT(Opportunities[opportunityid]), Opportunities, Opportunities[statecodename] = "Won", PREVIOUSQUARTER('Date Table'[Date]) )

But it doesn't give me the correct result, as supposedly based on the picture, I expected to have value of 2 (01/14/22 and 03/14/22).

Can I ask for suggestion which DAX actually more appropriate for this purposes ?

Thanks

Re: How to use PREVIOUSQUARTER

tamerj1 — Tue, 12 Apr 2022 05:45:30 GMT

Hi VoltesDev

In your visual you have to use the date column from date table not the actual close date from the data table

Re: How to use PREVIOUSQUARTER

VoltesDev — Tue, 12 Apr 2022 06:02:03 GMT

Hi tamerj1 ,

I changed it to use from Date table instead, but it doesn't look good either ->

Those are hierarchy from auto-date-hierarchy, but it refer to the correct date, but somehow the value is wrong.

In fact, If I included the Win Count measure, it will give me all the Date tables rows.

Thanks,

Re: How to use PREVIOUSQUARTER

tamerj1 — Tue, 12 Apr 2022 06:35:27 GMT

VoltesDev
Can you please share a sample file?

Re: How to use PREVIOUSQUARTER

VoltesDev — Tue, 12 Apr 2022 09:53:49 GMT

Hi, I want to, but how to share file (pbix) here ?

Thanks

Re: How to use PREVIOUSQUARTER

tamerj1 — Tue, 12 Apr 2022 10:07:55 GMT

VoltesDev

You may upload to WeTransfer for example and share the link

Re: How to use PREVIOUSQUARTER

VoltesDev — Tue, 12 Apr 2022 10:14:33 GMT

Hi,

I just setup Box, hopefully can do the same -> https://app.box.com/s/jvrp20g3hytyabusgewvxynbfkgysidd

So basically what I expected is (with the blue background chart), the guy CQ (current quarter) is 0 and PQ (previous quarter) is 2

Thanks

Re: How to use PREVIOUSQUARTER

tamerj1 — Tue, 12 Apr 2022 10:49:18 GMT

Hi VoltesDev
Uou don't have data for the previuos quarter! Do you mean same quarter perviuos year?

Re: How to use PREVIOUSQUARTER

VoltesDev — Tue, 12 Apr 2022 10:59:18 GMT

No, I mean Previous Quarter for this quarter. Actually now I have doubt, does QTD means like today is 12 April, so QTD is 1 Apr - 12 Apr ? So in that sense, what I want for Previous Quarter is the sum of 1 Jan - 30 Mar.

Re: How to use PREVIOUSQUARTER

tamerj1 — Tue, 12 Apr 2022 11:46:59 GMT

Hi VoltesDev
Please refer to the sample file with the solution https://www.dropbox.com/t/RftKbPYvOBYLJO4A
I have updated the Date table ti suit this type of calculation.
As long as Win/Lost is kept on the slicer there is no need to hard code it. You can simply switch between Lost and Win from the slicer. If you still need it the same way before I guess no issue for you to do that.
The count measure is simple distinct count. If a quarter is selected it will simply calculate the count for the selected quarter.
The Quarter to Year measure calculates the cumulative count staring from the start of the quarter up to the selected date. It will give the same result as the simple count except when you look at day or week level.
The previous quarter calculates the count of the selected date but shifted one quarter back in the time.
Same for the previous quarter to dater

Count = DISTINCTCOUNT ( Opportunities[opportunityid] )

Count PQ = VAR CurrentYearQuarterNumber = SELECTEDVALUE ( 'Date'[Year Quarter Number] ) VAR PreviousYearQuarterNumber = CurrentYearQuarterNumber - 1 VAR Result = CALCULATE ( DISTINCTCOUNT ( Opportunities[opportunityid] ), REMOVEFILTERS ( 'Date' ), 'Date'[Year Quarter Number] = PreviousYearQuarterNumber, VALUES ( 'Date'[Month In Quarter Number] ) ) RETURN Result

Count QTD = VAR LastMonthAvailable = MAX ( 'Date'[Year Month Number] ) VAR LastYearQuarterAvailable = MAX ( 'Date'[Year Quarter Number] ) VAR Result = CALCULATE ( DISTINCTCOUNT ( Opportunities[opportunityid] ), REMOVEFILTERS ( 'Date' ), 'Date'[Year Month Number] <= LastMonthAvailable, 'Date'[Year Quarter Number] = LastYearQuarterAvailable ) RETURN Result

Count PQTD = VAR CurrentYearQuarterNumber = SELECTEDVALUE ( 'Date'[Year Quarter Number] ) VAR PreviousYearQuarterNumber = CurrentYearQuarterNumber - 1 VAR Result = CALCULATE ( [Count QTD], REMOVEFILTERS ( 'Date' ), 'Date'[Year Quarter Number] = PreviousYearQuarterNumber, VALUES ( 'Date'[Month In Quarter Number] ) ) RETURN Result