Display Sales by Quarter

[Samurai_]

Hello,

I'm looking to try to display the total of sales within the last 4 quarters while keeping the same Carrier accross each quarter on the same line throughout the display. 
I tried using small multuiples however I couldnt get it to display correctly but still think small multuiples is the way to get it to work.
Here is a sample of data. The start is before or on the quarter start date and the end date is after that date. So if I was looking at this today 8/29/24, the following would show up in each section. 
Last Quarter: Policy 1 Policy 2 Policy 3 Policy 4 
2 Quarters Ago: Policy 1 Policy 2 Policy 4 
3 Quarters Ago: Policy 1 Policy 2 Policy 4 Policy 6
4 Quarters Ago: Policy 2 Policy 4 Policy 5 Policy 6

Above is what it looks like right now but what i'm trying to get it to look like is this (blocked names but kept a few letters to show that it is one carrier in the same position accross the whole row)

I'm still new to power bi so any help would be appreciated. 

[v-yangliu-msft]

Thanks for the reply from ahadkarimi , please allow me to provide another insight: 
Hi  @Samurai_ ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _date=
CALENDAR(
    DATE(2022,1,1),DATE(2026,12,31))
return
ADDCOLUMNS(
    _date,"Qu",YEAR([Date])&" Q"&QUARTER([Date]))

2. Create calculated column.

Qu =
CONCATENATEX(
    FILTER(ALL('Table 2'),
    'Table 2'[Date]>='Table'[Start Date]&&'Table 2'[Date]<='Table'[End Date]),'Table 2'[Qu],"-")

3. Enter data – flag_Table.

4. Create measure.

Flag_last1 =
var _today=TODAY()
var _lastdate=EOMONTH(_today,-3)
var _qu=YEAR(_lastdate)&" Q"&QUARTER(_lastdate)
return
SUMX(
    FILTER('Table',
CONTAINSSTRING(
    MAX('Table'[Qu]),_qu)=TRUE()),[Value])

Flag_last2 =
var _today=TODAY()
var _lastdate=EOMONTH(_today,-6)
var _qu=YEAR(_lastdate)&" Q"&QUARTER(_lastdate)
return
SUMX(
    FILTER('Table',
CONTAINSSTRING(
    MAX('Table'[Qu]),_qu)=TRUE()),[Value])

Flag_last3 =
var _today=TODAY()
var _lastdate=EOMONTH(_today,-9)
var _qu=YEAR(_lastdate)&" Q"&QUARTER(_lastdate)
return
SUMX(
    FILTER('Table',
CONTAINSSTRING(
    MAX('Table'[Qu]),_qu)=TRUE()),[Value])

Flag_last4 =
var _today=TODAY()
var _lastdate=EOMONTH(_today,-12)
var _qu=YEAR(_lastdate)&" Q"&QUARTER(_lastdate)
return
SUMX(
    FILTER('Table',
CONTAINSSTRING(
    MAX('Table'[Qu]),_qu)=TRUE()),[Value])

Measure =
SWITCH(
    TRUE(),
    MAX('Flag_Table'[Flag])="Flag1",[Flag_last1],
    MAX('Flag_Table'[Flag])="Flag2",[Flag_last2],
    MAX('Flag_Table'[Flag])="Flag3",[Flag_last3],
    MAX('Flag_Table'[Flag])="Flag4",[Flag_last4])

5. Result:

Best Regards,

Liu Yang

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

[Samurai_]

@v-yangliu-msft This is great and just what I was looking for!

It looks like there is something wrong with the way its summing within the graphs. For example Flag1 should not be 12k for Carrier 4. Carrier 4 should display 4.7k. 

[ahadkarimi]

Hi @Samurai_, try these measures below, and if you encounter any issues, let me know.

Last Quarter Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -1, QUARTER)
)

2 Quarters Ago Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -2, QUARTER)
)

3 Quarters Ago Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, QUARTER)
)

4 Quarters Ago Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -4, QUARTER)
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

[Samurai_]

I cant put measure data fields into "Small Multiples" section. Is there another way to display this?