Forum Discussion

VoltesDev's avatar
VoltesDev
Icon for Helper V rankHelper V
4 years ago
Solved

How to use PREVIOUSQUARTER

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's avatar
    tamerj1
    4 years ago

    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

9 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      VoltesDev
      Icon for Helper V rankHelper V
      4 years ago

      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,