previous quarter to date

[Anonymous]

Hi,

I am trying to calculate Current QTD and Previous Quarter's QTD. Current QTD is pretty straight forward but when I try to calculate PQTD I get full previous quarter numbers.

Ex: If Current quarter QTD is Q2: 4/1/2018 to 6/6/2018

     then Previous Quarter QTD Q1: 1/1/2018 to 3/6/2018

1. Current QTD = Calculate(SUM(Messages Table[Messages_column]),DIM_Date[date])

2. Previous Quarter = CALCULATE(SUM(Messages Table[Messages_column]),DATEADD(DIM_Date[date].[Date],-1,QUARTER))

3. Previous QTD = CALCULATE([PreviousQuarter],DATESQTD(DIM_Date[date]))

Thanks in advance

[MFelix · Accepted solution]

Hi @Anonymous,

First of all the measure you have for QTD is not calculation QTD you should use one of two formulas:

Current QTD = Calculate(TOTALQTD(SUM(Table2[Value]);DIM_Date[Date]) )

Current QTD = Calculate(SUM(Table2[Value]);DATESQTD(DIM_Date[Date]) )

Regarding the previous QTD when you use the DATEADD you will get all the all the dates that are in the time frame you have for this case you need to calculate the number of days that have pass over previous quarter and calculate the total for those number of days.

Try this measure:

Measure =
VAR Previous_Quarter =
    STARTOFQUARTER ( DATEADD ( DIM_Date[Date]; -1; QUARTER ) )
VAR Previous_Quarter_end =
    ENDOFQUARTER ( DATEADD ( DIM_Date[Date]; -1; QUARTER ) )
VAR Current_Quarter_Start =
    STARTOFQUARTER ( DIM_Date[Date] )
VAR Max_Date =
    MAX ( Table2[Date] )
RETURN
    CALCULATE (
        SUM ( Table2[Value] );
        DIM_Date[Date] >= Previous_Quarter
            && DIM_Date[Date]
                <= IF (
                    Previous_Quarter_end
                        <= Previous_Quarter + DATEDIFF ( Current_Quarter_Start; Max_Date; DAY );
                    Previous_Quarter_end;
                    Previous_Quarter + DATEDIFF ( Current_Quarter_Start; Max_Date; DAY )
                )
    )

This will give you the below result (for my values I have 100 for Q1, 200 Q2, 300 Q3, 400 Q4) as you can see previous quarte is half the value in the quarter.

 

I also added the PBIX attach so you can play around with the slicer

Regards

MFelix

[stephen-tracey]

Nice.. Thanks so much.. very handy

[Anonymous]

Hi @MFelix,

1. Current QTD measure was a copy paste error  but I did calculate it using DATESQTD function, no worries there. 

2. Thanks for the PQTD measure. It is working fine but when I add Quarter from DIM_DATE into the X-axis of the chart PQTD numbers disappear. I am trying to figure out the issue but please take a look at the below screenshots:

With and Without Quarter in X-axis:

[Anonymous]

Hey @MFelix,

I figured it out. Thanks for the help...cheers