Table visual with date columns: handling invidual months and a group months in the same table

[Kinsley]

I have one table visual that contains:

• Rows: companies names
• Columns:  10 columns for Mar- Dec/2023
• Values: Sum of sales quantities

My data contains sales from June/2022 - Dec 2023. Currently Im filtering the visual on all the months in 2023.

Question: I want to add one extra column that is the sum of sales in all the past months (June/2022- Feb/2023)- call this past period. 

How do we do this? and ideally dynamically?

TIA! 

[v-jianboli-msft · Accepted solution]

Hi @Kinsley ,

Based on your description, I have created a simple sample:

Please try:

First add a new column:

Period = 
var _a = FORMAT([Date],"MMM-YY")
return IF([Date]>=DATE(2022,6,1)&&[Date]<=DATE(2023,2,28),"Past Period",_a)

Sort = IF([Date]<=DATE(2023,2,28),1,MONTH([Date]))

Output:

Then Sort the[Period] column by [Sort] column:

Apply it to the matrix visual:

Final output:

Best Regards,

Jianbo Li

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