How to use PREVIOUSQUARTER

[VoltesDev]

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

[tamerj1]

Hi @VoltesDev 

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

[VoltesDev]

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,

[tamerj1]

@VoltesDev 
Can you please share a sample file?

[VoltesDev]

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

Thanks

[tamerj1]

@VoltesDev 

You may upload to WeTransfer for example and share the link

[VoltesDev]

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

[tamerj1]

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

[VoltesDev]

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.

[tamerj1 · Accepted solution]

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