Forum Discussion
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 | FEB |
| Yellow | FEB |
| Green | JAN |
| Green | FEB |
I need an outcome that includes the totals per month.
| Date | Colour | Total/Count |
| JAN | Red | 2 |
| JAN | Green | 1 |
| JAN | Blue | 1 |
| FEB | Red | 1 |
| FEB | Yellow | 2 |
| FEB | Green | 1 |
I thought about grouping however the data will be streaming. The colours will be constantly changing.
The outcome is to use monthly trend line to interpret the data.
Many thanks
Hi, Anonymous
You can try the following methods.
- 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.
3 Replies
- TheoC
Community Champion
Hi Anonymous
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
v-zhangtiCommunity Support
Hi, Anonymous
You can try the following methods.
- 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
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
