Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculating Last Quarter to Date

The goal is to compare sales from this current quarter to date vs last quarter to date. Problem is that my current measure will calculate the entire month's sales when to date (Dec 2 = two days of sales) are needed. I have a Calendar table with fiscal year/quarters defined. Below will be sample data along with the Fiscal Quarter to Date measure + Last Quarter to Date measure and expected output. Your advice is greatly appreciated. 

 

Fiscal Quarter to Date (FQTD). This correctly calculates sales to date.

 

Sales - FQTD = 
CALCULATE(
    SUM( 'Sales'[Sales] )
    ,FILTER(
        ALL( 'Calendar' )
        ,'Calendar'[Date] <= MAX( 'Calendar'[Date] ) 
            && 'Calendar'[Fiscal YearQuarterNumber] = MAX( 'Calendar'[Fiscal YearQuarterNumber] )
        )
    )

 

Last Quarter to Date (LQTD). This does not calculate sales to date. It will give the last quarter's entire month's sales.  Advice to update this will be greatly appreciated

 

Sales - LQTD = 
CALCULATE(
    [Sales - FQTD]
    ,DATESQTD(
        DATEADD(
            'Calendar'[Date]
            ,-1
            ,QUARTER
            )
        )
    )

 

Sample Data: **Note** Dec 2024 sales are quite small compared to other months. This is because we are 2 days into the month. 

DateSales - FQTD
Dec 202442
Nov 2024102,156
Oct 202459,598
Sep 202432,880

Expected Output: **Note** Since today = Dec 2, the goal is to get the first 2 days sales of the previous quarter (Sep 1 + 2) >> This amount should = 1,667 (Sep 1 = 1,142 / Sep 2 = 525).  

DateSales - FQTDSales - LQTD
Dec 2024421,667
Nov 2024102,156 
Oct 202459,598 
Sep 202432,880 
Need Help
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

    As the months in your fiscal quarter are the same months in a calendar quarter, you can use a combination of TOTALQTD and DATEADD or EDATE, LASTDATE,  STARTOFQUARTER and DATESBETWEEN. It is another story if your quarter starts, say, in Feb.

    Previous QTD = 
CALCULATE (
    TOTALQTD ( [Total Revenue], Dates[Date] ),
    DATEADD ( Dates[Date], -1, QUARTER )
)

Previous QTD2 = 
CALCULATE (
    [Total Revenue],
    DATESBETWEEN (
        Dates[Date],
        EDATE ( STARTOFQUARTER ( Dates[Date] ), -3 ),
        EDATE ( LASTDATE ( Dates[Date] ), -3 )
    )
)

    Make sure your calendar table has been marked as a dates table.

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    This is a standard "YoYTD"  pattern.  Define what "the latest date" means for you. Then shift that date back x months (for example three months).  The result will be a combination of two filters, one to shift the calendar by the same amount, and the other by limiting the results to dates smaller than (or equal to)  what you calculated in the first step.

     

    Say your latest date is Dec 2nd.  so your filters would be 

     

    DATEADD(Calendar[Date],-3,MONTH)

     

    and 

     

    Calendar[Date]<= dt"2024-09-02"

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

    As the months in your fiscal quarter are the same months in a calendar quarter, you can use a combination of TOTALQTD and DATEADD or EDATE, LASTDATE,  STARTOFQUARTER and DATESBETWEEN. It is another story if your quarter starts, say, in Feb.

    Previous QTD = 
CALCULATE (
    TOTALQTD ( [Total Revenue], Dates[Date] ),
    DATEADD ( Dates[Date], -1, QUARTER )
)

Previous QTD2 = 
CALCULATE (
    [Total Revenue],
    DATESBETWEEN (
        Dates[Date],
        EDATE ( STARTOFQUARTER ( Dates[Date] ), -3 ),
        EDATE ( LASTDATE ( Dates[Date] ), -3 )
    )
)

    Make sure your calendar table has been marked as a dates table.