Forum Discussion
Samurai_
2 years agoRegular Visitor
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 sma...
Anonymous
2 years agoNot applicable
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_
2 years agoRegular Visitor
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.