Current year Q3 sales vs last year Q4 sales measure

[Snagalapur]

Hi All,

Please help on below measure – Current year Q3 Sales vs last year Q4 Sales, in below format.

Note: also is there possibility to dynamically change Quarters 2024 vs Quarters 2023  based on drop down?

my data set is in below format

Thank you.

[v-veshwara-msft]

Hi @Snagalapur 
We haven't heard from you. Has any answer met your requiremnt. If so could you consider accepting as solution to help other members find it easily.

Thank you.

[SacheeTh]

I have done this in a long time back, but not in a dynamical way, its kind a hardcoded Quater but To replicate the format in the provided table and dynamically compare sales between different periods (e.g., Current Year Q3 vs Last Year Q4), you can implement the following steps in Power BI:

---

Step 1: Create Measures for Each Period Comparison

1. Q3 (2024) vs Q4 (2023):

   Q3vsQ4Sales = 
   VAR CurrentYearQ3Sales = 
       CALCULATE(
           SUM(Sales[SalesAmount]),
           'Calendar'[Year] = 2024,
           'Calendar'[Quarter] = "Q3"
       )
   VAR LastYearQ4Sales = 
       CALCULATE(
           SUM(Sales[SalesAmount]),
           'Calendar'[Year] = 2023,
           'Calendar'[Quarter] = "Q4"
       )
   RETURN
       CurrentYearQ3Sales - LastYearQ4Sales

2. P07 (2024) vs P09 (2023):

   P07vsP09Sales = 
   VAR CurrentYearP07Sales = 
       CALCULATE(
           SUM(Sales[SalesAmount]),
           'Calendar'[Year] = 2024,
           'Calendar'[Period] = "P07"
       )
   VAR LastYearP09Sales = 
       CALCULATE(
           SUM(Sales[SalesAmount]),
           'Calendar'[Year] = 2023,
           'Calendar'[Period] = "P09"
       )
   RETURN
       CurrentYearP07Sales - LastYearP09Sales

3. Repeat similar measures for the other columns (e.g., P08 vs P11, P09 vs P12).

---

Step 2: Create a Dynamic Dropdown for Period Selection

1. Add a Parameter Table: Create a table to allow users to select dynamic periods:

   PeriodSelector = 
   DATATABLE(
       "Period", STRING,
       {
           {"Q3 vs Q4"},
           {"P07 vs P09"},
           {"P08 vs P11"},
           {"P09 vs P12"}
       }
   )

2. Add a Slicer: Add this table as a slicer in your report.

3. Use a Dynamic Measure: Create a measure to return values dynamically based on the selected period:

   DynamicComparison = 
   SWITCH(
       SELECTEDVALUE(PeriodSelector[Period]),
       "Q3 vs Q4", [Q3vsQ4Sales],
       "P07 vs P09", [P07vsP09Sales],
       "P08 vs P11", [P08vsP11Sales],
       "P09 vs P12", [P09vsP12Sales],
       BLANK()
   )

---

Step 3: Create a Table Visual

1. Add a matrix visual.
2. Use Market and Product as rows.
3. Add the measure [DynamicComparison] to the values field.
4. Apply the slicer to dynamically show the comparison for the selected period.

---

Step 4: Adjust Totals

If totals need adjustments, use measures to calculate them separately for consistency.

This will allow you to replicate the exact format and dynamically switch between periods based on dropdown selections. 

Let us know if you need further customization! Meanwhile I'll try to replicate this and give you a dynamical selection 

[Snagalapur]

Thank you for details, could you please suggest on account selection how to incorporate in measure as i have separate acount column for sale and AR values.

[SacheeTh]

To Add an account selection dynamically into your measures, you can use the Account column as a slicer in your Power BI report. if you would use measure with addding the Account column in a table should separate into accounts and give account wise results.

Esle, Users can select "Sales" or "AR" to filter in a slicer from the data, and this selection can be integrated into your measures.

For example, to calculate the comparison between Q3 (2024) and Q4 (2023), you can use the SELECTEDVALUE function to dynamically pick the selected account from the slicer. A measure for Q3 vs Q4 might look like this:

Q3vsQ4SalesAR = 
VAR SelectedAccount = SELECTEDVALUE('YourTable'[Account]) -- Dynamically selects "Sales" or "AR"
VAR CurrentYearQ3 = 
    CALCULATE(
        SUM('YourTable'[Actuals 2024]),
        'YourTable'[Quarter] = "Q3",
        'YourTable'[Account] = SelectedAccount
    )
VAR LastYearQ4 = 
    CALCULATE(
        SUM('YourTable'[Actuals 2023]),
        'YourTable'[Quarter] = "Q4",
        'YourTable'[Account] = SelectedAccount
    )
RETURN
    CurrentYearQ3 - LastYearQ4

Similarly, create measures for other comparisons like P07 vs P09 or P08 vs P11. You can integrate period selection dynamically using a parameter table and the SWITCH function. For example:

DynamicComparison = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(PeriodSelector[Period]) = "Q3 vs Q4", [Q3vsQ4SalesAR],
    SELECTEDVALUE(PeriodSelector[Period]) = "P07 vs P09", [P07vsP09SalesAR],
    SELECTEDVALUE(PeriodSelector[Period]) = "P08 vs P11", [P08vsP11SalesAR],
    SELECTEDVALUE(PeriodSelector[Period]) = "P09 vs P12", [P09vsP12SalesAR],
    BLANK()
)

Try adding a slicer for the Account column, allowing users to filter by "Sales" or "AR." Use this slicer alongside the period parameter slicer to create a dynamic report. Totals will automatically respect the slicer selections as long as they are incorporated into your visual filters. This approach enables flexible period and account-based comparisons in your Power BI report.

[Snagalapur]

Thank you for help, I have 2 questions

1. I’m  unable to get product column separately in Matrix
2. I did add values one by one based on measure , is there any way to add all columns at once
3. 

[v-veshwara-msft · Accepted solution]

Hi @Snagalapur ,

Thank you for reaching out to the Microsoft Fabric Community Forum. Based on the steps provided by @SacheeTh, 
1.We have replicated the steps and successfully reproduced the result in Power BI Desktop, as shown in the image below.

2. To add all measures into columns at once you can use Field Parameters

 Steps:

       >Go to Modeling section in Power bi and select Fields under New Parameter.

       >Choose a name for the parameter and select the measures you want to add as fields.

       >Disable 'Add slicer to this page' (You can keep it if you wish) then click create.

This will combine all the measures you have added and create a new table.

Now you can drag the Parameter into the Values section which will add all the measures as columns into the Matrix visual.

If you encounter any further issues, please feel free to reach out. If this solution works for you, kindly accept it so that others can benefit from it as well.

Best Regards,
Vinay kumar Eshwara.

[SacheeTh]

1. Pls try using the Outline or Tabular mode from the Layout & style in visual Formatting.

2. Try removing the Blank() from the DynamicComparison Measure.
Else, Select Show items with no data option from the Field options.

let me know if got this 😎