cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rcharan Regular Visitor
Regular Visitor

previous quarter to date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: previous quarter to date

Hi @rcharan,

 

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.

 qrd.png

 

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

 

 

Regards

 

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: previous quarter to date

Hi @rcharan,

 

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.

 qrd.png

 

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

 

 

Regards

 

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




rcharan Regular Visitor
Regular Visitor

Re: previous quarter to date

Hi @MFelix,

 

1. Current QTD measure was a copy paste error Smiley Wink 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:

With Quarter.PNGWithout Quarter.PNG

 

 

rcharan Regular Visitor
Regular Visitor

Re: previous quarter to date

Hey @MFelix,

 

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