topic Re: Display Sales by Quarter in DAX Commands and Tips

Display Sales by Quarter

Samurai_ — Thu, 29 Aug 2024 20:49:48 GMT

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.

Re: Display Sales by Quarter

ahadkarimi — Thu, 29 Aug 2024 19:55:16 GMT

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!

Re: Display Sales by Quarter

Samurai_ — Thu, 29 Aug 2024 20:24:38 GMT

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

Re: Display Sales by Quarter

Anonymous — Fri, 30 Aug 2024 09:25:33 GMT

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

Re: Display Sales by Quarter

Samurai_ — Thu, 05 Sep 2024 16:20:15 GMT

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.