Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Month per month trend

Hi there, Looking for a solution to compile a month by month trend report. So as an example using the following data:   Colour Date Red JAN Red JAN Red FEB Blue JAN Yellow ...
Need Help
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    1. Based on the original table, output a new table, as shown in the figure.

        

    Table 2 =
SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Colour] )

     

        2. Create a calculation column to calculate the total number of colors per month.

    Count =
CALCULATE (
    COUNT ( 'Table'[Colour] ),
    FILTER (
        'Table',
        'Table'[Colour] = EARLIER ( 'Table 2'[Colour] )
            && 'Table'[Date] = EARLIER ( 'Table 2'[Date] )
    )
)

     

       3.  I wonder if this is the visual effect you want.

    Best Regards,

    Community Support Team _Charlotte

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

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

Hi @chinny 

 

You will need to ensure you have a valid Date in the Date column.  From there, you can use a simple SUM measure to calculate the total count.  You can add this to the Values in your respective chart, add Colors to the legend, and the Date as the X Axis.  So, ideally, you can perform the following steps:

 

1. New Measure = SUM ( 'TableName'[Count] )

2. Add Line or Bar Chart (or other chart you want) to the canvas.

3. Add fields to your visual similar to the below:

 

This should help get you where you need 

 

 

Hope this helps.

Theo