Converting Year-to-date values to Quarterley

Power BI
1

Hi All,
I would like to convert Year-to-date values to Quarterley based on a December Year-End.

Here is the measure for the underlying values:

VAR PQDate = Switch( Month( Previous Quarter( Date Table(Date) ) ) , 12 , 0 , Previous Quarter( Date Table(Date) ) )
VAR CQ = Sum(Value)
VAR PQ = Calculate( Sum(Value),DateTable(Date)=PQDate)

Return
CQ-PQ

This works well for the underlying values however the subtotal is the total of the CQ values across the dates.

Any ideas on how to resolve this for the total, or a more elegant way to derive the QoQ values would be greatly appreciated.

2

Hello @123kerrod,

Thank You for posting your query onto the Forum.

If you want to convert your values from Year-To-Date to Quarterly than you can use the DATESQTD or TOTALQTD functions. Also if you’re looking to fix the incorrect totals than I’m providing a link below which was created by one of our expert member @Greg onto this topic.

If this doesn’t solve your problem than please provide the working PBIX file for the reference so that our members of the forum can assist you in a better and efficient manner. If your data contains the sensitive information than you can also mask the sensitive data. Below is the link provided about how to mask the sensitive data.

Thanks and Warm Regards,
Harsh

3

Thanks for the prompt reply.

TotalQTD appears that it calculates data in a date range, with the time intelligence being pre-defined for the quarters. The data I am using is prepared/extracted once a quarter as a YTD number. Naturally, QoQ values are then derived from subtracting the prior quarter from the current by only changing the date filter. Is there a more elegant way of doing this?

Appreciate the links, they are a great resource.

4

Hi @123kerrod. Can you prepare a sample in Excel that would have some raw data and a mock-up of what you’re looking for for the forum members to pursue further?
Greg

5

Please find attached some dummy data in a similar format, and a pivot table with the expect output.
20210124 QoQ Amount Sample.xlsx (19.4 KB)
Let me know if I need to clarify further.

6

Hello @123kerrod,

Thank You for providing the mock-up file alongwith the results that you’re trying to achieve.

Well, this solution will require a combination of Power Query and DAX.

Let’s first move to the Power Query side -

Since there are few multiple records for the same date against the Product ID and Customer ID such as for example Date = 30/09/2020, Product ID = 1 and Customer ID = 1. So firstly we’ll be required to “GROUP” the data. Below is the screenshot of the “Grouping” provided for the reference -

Grouping of Data
Grouping of Data875×617 11.7 KB

And now, once the data is grouped as per the above screenshot second step is to add the “Index” column based onto the “Date” field. Below is the screenshot provided for the reference -

Index Column
Index Column1155×565 21.6 KB

So before closing the Power Query the result will be like this. Below is the screenshot provided for the reference -

Power Query
Power Query1048×784 29.7 KB

Now, once the data is loaded into the model. We’ll be required to create a “Calculated Column”. Below is the formula provided for the referemce -

SALES DIFFERENCE = 
VAR myindex = 'Actual Data'[Index]

VAR mycustomer = 'Actual Data'[Customer ID]

VAR myproducts = 'Actual Data'[Product ID]

VAR previousindex =
    CALCULATE (
        MAX ( 'Actual Data'[Index] ) ,
            FILTER ( 'Actual Data' , 
                'Actual Data'[Customer ID] = mycustomer && 
                'Actual Data'[Product ID] = myproducts &&
                'Actual Data'[Index] < myindex )
    )
VAR previoussales =
    CALCULATE (
        MAX ( 'Actual Data'[Total Sales] ) ,
            FILTER (
                'Actual Data' ,
                'Actual Data'[Index] = previousindex &&
                'Actual Data'[Customer ID] = mycustomer &&
                'Actual Data'[Product ID] = myproducts
        )
    )
RETURN
    IF ( previoussales , 'Actual Data'[Total Sales] - previoussales )

Now, once the calculated column is created you’ll be able to see the differential numbers based onto the quarters. Below is the screenshot provided for the reference -

Resultant Output
Resultant Output820×443 13.2 KB

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

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

Thanks and Warm Regards,
Harsh

Quarter-On-Quarter Calculation.pbix (95.1 KB)

7

Thanks for the detailed response.

The use of an index column is a creative one. This solution does not work where the YTD value goes to zero and the mvmt would need to be negative PQ balance (Which is the perpetual issue!)

I have resolved it in PowerQuery by splitting the date into Year & Month.Day. Pivot the Month.Day, replace values and add calculated columns.

Thanks for the help!