Quarter over Quarter - the quarter value is the last month in the quarter

chad.sharpe · July 24, 2022, 4:37pm

Hi,
The data set is such that the $ amount month over month should not be summed. The last month in the quarter should be the quarters amount.

Our fiscal starts November 1st.
Q1 is nov/dec/jan - however when doing Q over Q, the total for Q1 should be Jan. Not the sum of each month.
Essentially i need to pull the last month out of each quarter. For the current Quarter, if we are only one month into it, then i need to use the first month to compare to the prior Q.

I’ve played with parellel periods, lastnonblank. I can’t figure out a method that works.

I’ve got a date table and a Qoffset added as well.

I want to show a proper quarter over quarter variance graph.

Quarterly PCR.xlsx (16.9 KB)
eDNA PCR Quarter.pbix (90.0 KB)

Harsh · July 25, 2022, 12:49pm

Hello @chad.sharpe,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the condition that you’ve specified. Below are the steps listed as follows -

1). Remove bi-directionality relationship between the Fact Table and Dates Table. Below is the screenshot of the revised Data Model provided -

2). In the second step, you’ll be required to create a calculated column of “FiscalMonthnYear”. Below is the DAX formula alongwith the screenshot of the calculated column provided -

FiscalMonthnYear =
VAR _Fiscal_Month_Number =
SWITCH( TRUE() ,
    Dates[MonthOfYear] = 11 , "01" ,
    Dates[MonthOfYear] = 12 , "02" ,
    Dates[MonthOfYear] = 1 , "03" ,
    Dates[MonthOfYear] = 2 , "04" ,
    Dates[MonthOfYear] = 3 , "05" ,
    Dates[MonthOfYear] = 4 , "06" ,
    Dates[MonthOfYear] = 5 , "07" ,
    Dates[MonthOfYear] = 6 , "08" ,
    Dates[MonthOfYear] = 7 , "09" ,
    Dates[MonthOfYear] = 8 , "10" ,
    Dates[MonthOfYear] = 9 , "11" ,
    Dates[MonthOfYear] = 10 , "12" )

VAR _Fiscal_Year =
LEFT( Dates[FQ in FY] , 4 )

VAR _Results =
VALUE( _Fiscal_Year & _Fiscal_Month_Number & "00" )

RETURN
_Results

3). Now, we’ll create the measures to achieve the results. Below are the measures provided -

CQ Sales =
VAR _Current_Quarter =
CALCULATE( MAX( Dates[FiscalMonthnYear] ) ,
    ALLEXCEPT( Dates , Dates[FQ in FY] ) ,
    VALUES( Dates[FQ in FY] ) )

VAR _Last_Sales_Date =
CALCULATE( LASTDATE( Sheet1[Date Column] ) ,
    ALL( Sheet1 ) )

VAR _Sales_On_Last_Date =
CALCULATE( [Total Spend] ,
    FILTER( Dates ,
        Dates[Date] = _Last_Sales_Date ) )

VAR _Current_Quarter_Sales =
CALCULATE( [Total Spend] ,
    FILTER( Dates ,
        Dates[FiscalMonthnYear] = _Current_Quarter ) )

RETURN
_Current_Quarter_Sales + _Sales_On_Last_Date

LQ Sales =
VAR _Last_Sales_Date =
CALCULATE( LASTDATE( Sheet1[Date Column] ) ,
    ALL( Sheet1 ) )

VAR _Last_1_Month_Sales =
CALCULATE( [CQ Sales] ,
    DATEADD( Dates[Date] , -1 , MONTH ) )

VAR _Last_2_Months_Sales =
CALCULATE( [CQ Sales] ,
    DATEADD( Dates[Date] , -2 , MONTH ) )

VAR _Last_3_Months_Sales =
CALCULATE( [CQ Sales] ,
    DATEADD( Dates[Date] , -3 , MONTH ) )

VAR _Results =
SWITCH( TRUE() ,
    MIN( Dates[Date] ) > _Last_Sales_Date , BLANK() ,

    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 11 , _Last_1_Month_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 12 , _Last_2_Months_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 1 , _Last_3_Months_Sales ,

    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 2 , _Last_1_Month_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 3 , _Last_2_Months_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 4 , _Last_3_Months_Sales ,
       

    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 5 , _Last_1_Month_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 6 , _Last_2_Months_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 7 , _Last_3_Months_Sales ,

    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 8 , _Last_1_Month_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 9 , _Last_2_Months_Sales ,
    MIN( Dates[Date] ) = _Last_Sales_Date && MONTH( _Last_Sales_Date ) = 10 , _Last_3_Months_Sales ,

_Last_3_Months_Sales )

RETURN
_Results

4). Finally, after writing the above measures we’ll be able to see the individual line item results but figures at Grand Total level will be incorrect. So finally, we’ll write two small measures for each of them just to fix the totals. Below are the measures provided -

CQ Sales - Totals =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Dates ,
            Dates[FQ in FY] ,
            Dates[Month & Year] ) ,
        "@Totals" ,
        [CQ Sales] ) ,
    [@Totals] )

LQ Sales - Totals =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Dates ,
            Dates[FQ in FY] ,
            Dates[Month & Year] ) ,
        "@Totals" ,
        [LQ Sales] ) ,
    [@Totals] )

And now, we’ll get the results as expected. Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Note: I’ve added “Month & Year” field in order to cross-check the results. In case, if you want to remove that field, just adjust the logic of the Totals measure accordingly.

Thanks and Warm Regards,
Harsh

eDNA PCR Quarter - Harsh.pbix (97.4 KB)

chad.sharpe · July 25, 2022, 8:04pm

This is really detailed in logic. I’ll transpose the logic onto my bigger dataset.

When putting this in a bar chart, the final month, in this case June does not appear. The databar appears, but the month name does not? Any idea why that might be?

Harsh · July 26, 2022, 2:22pm

Hello @chad.sharpe,

I guess, you’re referring to this issue. Below is the screenshot attached -

The reason being is, the field on the X-Axis is in the “Continuous” form and it has broken the period in the multiple of 3’s. You can change it to “Categorical” form and then it’ll start to show June as a month name. See this -

Rather than this, create a hierarchical chart by dragging “Year” & “Month” and then turn-off the “Concatenate Labels” option under the X-Axis which will look better than the previous visual. Below is the screenshot provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Thanks and Warm Regards,
Harsh

eDNA PCR Quarter - Harsh v2.pbix (97.9 KB)

chad.sharpe · July 26, 2022, 2:35pm

Very interesting… that solves a few issues in some of my prior reports as well…

Thanks