Create Column that Shows an Increase or Decrease in Volumes per Product by Reporting Period

[MikePowerBI]

Hi there,

I have a dataset similar to the test data I created in the screenshot below. I am looking to create a column (either in DAX or in the Power Query Editor) which has a similar result to the "Calculated Column".

The goal is to create a column that showcases if a product has had an increase or decrease in its volume when compared to the previous reporting period. For example, based on the test data in the screenshot: Product C had volumes of 50, 70, and 60 in the reporting periods of December 2023, January 2024, and February 2024 respectively. As a result, the reporting period of January 2024 would say "Increase" (increase from 50 to 70) and the reporting period of February 2024 would say "Decrease" (decrease from 70 to 60). 

Any help would be greatly appreciated! 

[v-weiyan1-msft · Accepted solution]

Hi @MikePowerBI ,

Based on your description and sample data provided,
you might consider using the following code to create Calculated column.

Change in Volume = 
VAR CurrentProduct = 'Table'[Product]
VAR CurrentPeriod = 'Table'[Reporting Period Order]
VAR CurrentVolume = 'Table'[Volume]
VAR PreviousVolume = 
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        FILTER (
            'Table',
            'Table'[Product] = CurrentProduct
                && 'Table'[Reporting Period Order] = CurrentPeriod - 1
        )
    )
RETURN
    IF (
        PreviousVolume = BLANK (),
        BLANK (),
        IF ( CurrentVolume > PreviousVolume, "Increase", "Decrease" )
    )

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Previous volume = if(ISBLANK(CALCULATE(MAX(Data[Reporting Period]),FILTER(Data,Data[Product]=EARLIER(Data[Product])&&Data[Reporting Period]<EARLIER(Data[Reporting Period])))),BLANK(),LOOKUPVALUE(Data[Volume],Data[Reporting Period],CALCULATE(MAX(Data[Reporting Period]),FILTER(Data,Data[Product]=EARLIER(Data[Product])&&Data[Reporting Period]<EARLIER(Data[Reporting Period]))),Data[Product],Data[Product]))

Column = if(ISBLANK(Data[Previous volume]),BLANK(),if(Data[Volume]>Data[Previous volume],"Increase","Decrease"))

Hope this helps.

[MikePowerBI]

Hi @Ashish_Mathur 

This solution also worked for me. Thank you very much for your support!

[Ashish_Mathur]

You are welcome.

[v-weiyan1-msft · Accepted solution]

Hi @MikePowerBI ,

Based on your description and sample data provided,
you might consider using the following code to create Calculated column.

Change in Volume = 
VAR CurrentProduct = 'Table'[Product]
VAR CurrentPeriod = 'Table'[Reporting Period Order]
VAR CurrentVolume = 'Table'[Volume]
VAR PreviousVolume = 
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        FILTER (
            'Table',
            'Table'[Product] = CurrentProduct
                && 'Table'[Reporting Period Order] = CurrentPeriod - 1
        )
    )
RETURN
    IF (
        PreviousVolume = BLANK (),
        BLANK (),
        IF ( CurrentVolume > PreviousVolume, "Increase", "Decrease" )
    )

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikePowerBI]

Hi @v-weiyan1-msft 

Thank you for your quick response and your support!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...