Forum Discussion
Display Sales by Quarter
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.
4 Replies
- ahadkarimi
Solution Specialist
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!I cant put measure data fields into "Small Multiples" section. Is there another way to display this?
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
Anonymous 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.
