Showing Partial Quarter and Full Quarter values in same visual with previous year's Partial Quarter

[samratpbi]

In this article, I would like to demonstrate a scenario of showing Quarterly data in a simple column chart. However with an added requirement to identify when a quarter is not complete and also show the latest and previous quarter's data accordingly.

So, below is what we need:

lets try to break down the problem and try to understand the critical requirements.

1. the data is monthly and need to show the previous quarter's data. Hence, we definitely need a date dimension table.

2. Since need to do some quarterly calculations, hence need some specific columns in the date dimension table

3. Once those columns were created, next challenge was to update the x-axis quarter values. If the Quarter is not complete (i.e. if we have data lets say upto Feb, then show partial)

4. Next step is to if current quarter is partial then show partial in the quarter name for same quarter last year too.

5. Final step is if the current quarter is partial, then for same quarter last year, update the calculation to show only upto those months. i.e. if for current quarter we have data upto April'25, then for 2024, 2nd quarter show only data upto April'24 (for 2nd quarter) for a true comparison; as comparing a partil and full quarter will not give a real insight of the data.

I tried to create a simple dataset to demonstrate the solution. Below is the monthly Sales table.

Now, lets create a date dimension table using Power query. We can use the organization's standard date dimension or may create one using Power Query. I have attached the M query for the date dimension, however few columns are required we will mention below.

Need QuarterID (e.g. 202501 for 1st 3 months of the 2025), End of Quarter Month id (e.g. 202503 for 1st 3 months of 2025), MonthQuarterNo (e.g. month no in a quarter like 1,2,3 for Jan, Feb, Mar for 1st Quarter & again 1,2,3 for Apr, May, Jun for 2nd Quarter) .

I created some measures and additional columns also (pbix file attached).

below column will give Quarter values in display format:

QtrYear = "Q" & d_Date[QtrNo] & "'" & RIGHT(d_Date[Year],2)

Then created a column to return max quarter:

MaxQtr_check =

CALCULATE(

    MAX(d_Date[QuarterID]),

    ALL(d_Date),

    d_Date[MonthID] = MAX(Sales[MO_ID])

)

Next, created a max sales month id column:

Max_Sales_MonthID = MAX(Sales[MO_ID])

Then 2 most important ones:

PartialQtr_check =

IF(

    d_Date[Max_Sales_MonthID] = d_Date[EndofQtrMonthID], 1, 0

)

The above column checks if max sales month matches with any end of quarter month id or not , i.e. whether its full quarter or not. If full quarter, then returns 1 otherwise 0.

QtrYear_partial =

VAR _fullQtr = d_Date[QtrYear]

VAR _partCY =

IF(

    CALCULATE(

        MAX(d_Date[PartialQtr_check]),

        ALL(d_Date)

    ) = 0 &&

    d_Date[QuarterID] = MAX(d_Date[MaxQtr_check]),

    " Partial*",

    ""

)

VAR _partPY =

IF(

    CALCULATE(

        MAX(d_Date[PartialQtr_check]),

        ALL(d_Date)

    ) = 0 &&

    d_Date[QuarterID] = MAX(d_Date[MaxQtr_check]) - 100 ,

    " Partial*",

    ""

)

RETURN

_fullQtr & _partCY & _partPY

Above calculated column checks if there is any partial quarter exists or not. If yes then it adds the text "Partial*" at the end of the current quarter and also end of same quarter last year. 

We will now use this in X-axis. Definitely this needs to be sorted properly to show in visual. Hence will sort this by another column which is quarter id.

We are almost there. If we use Total sales in Y-axis and the new column in X-axis it will show the data. However only for previous year's same quarter, it would show whole quarter value, which would not be correct comparison with current partial quarter. Hence we need to update the Total sales measure a little.

Total Doses Partial =

IF(

    CONTAINSSTRING(

        SELECTEDVALUE(d_Date[QtrYear_partial]),

        "Partial*"

    ),

    CALCULATE(

        [Total Sales],

        FILTER(

            d_Date,

            d_Date[MonthQtrNo] <=[Max MonthQ ID]

        )

    ),

    [Total Sales]

)

The above measure checks if the X Axis contains Partial text or not, if not, then show Total Sales, if it contains the word Partial then show data upto the max month number within a quarter. So if we have data upto April 25, hence max month no in that quarter is 1 (1-April, 2-May, 3-Jun) Hence for Q2'24 Partial, it will show upto April'24 data instead of full quarter.

Max MonthQ ID =

CALCULATE(

    MAX(d_Date[MonthQtrNo]),

    FILTER(

        ALL(d_Date),

        d_Date[MonthID] = [Max MonthID Sales]

    )

)

Finally below is the output, when we have data upto March 25:

And when we have data upto April 25

d_date_PowerQuery.zip

PartialQtr.pbix