Forum Discussion

Kinsley's avatar
Kinsley
Frequent Visitor
3 years ago
Solved

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

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! 

 

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.