How to calculate trend based on 3 last month and display in table

[mr_oli]

Hello guys!

I have to calculate trend based on last 3 months and then (if possible) display that trend as arrow (red, orange, black) in table.

My data looks like this:

Supplier	Date	Defect Type
A	01.11.2020	4
B	01.10.2020	3
B	02.10.2020	2
B	03.10.2020	1
B	01.11.2020	1
B	02.11.2020	1
B	01.12.2020	4
C	01.11.2020	5

and i want to achive sth like this:

btw - how to add pbix file here?

[v-easonf-msft · Accepted solution]

Hi, @mr_oli 

You can try to  create a summarize calculated table  :

New Table =
SUMMARIZE (
    'Table',
    'date'[Date],
    'date'[Date].[Year],
    'date'[Date].[Month],
    'date'[Date].[MonthNo],
    'Table'[Supplier],
    "Count Defect Type", COUNT ( 'Table'[Defect Type] )
)

 Then create a calculated column applied to the icon conditional formatting:

Percentage = 
IF (
    ISBLANK ( 'New Table'[Previous Count Defect Type] ),
    BLANK (),
    'New Table'[Count Defect Type] / 'New Table'[Previous Count Defect Type]
)

Please check my sample pbix for more details.

If it doesn't meet your requirement, please let me know .You can also upload your sample file to Onedrive for Bussiness then share the link here.

Best Regards,
Community Support Team _ Eason

[parry2k]

@mr_oli Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

@mr_oli , what is the basis of arrow and color. Month vs month. Three month avg vs current month , three vas last three ?

[mr_oli]

thank you for you answer

I discovered that comparing 3 months wont work, so I need to compare last month versus -2 month from current date

I think that I will use solution from this link:
https://community.powerbi.com/t5/Desktop/Unable-to-display-trend-arrows-in-table-visual/td-p/138572 

But the problem is that I can't create a measure to sum number of defects from previous date range (in my example whole month) which will be looking for current date.

I tried something like this:

Defects Previous Mo = CALCULATE(SUM('table'[Number of Defects]),DATEADD('date'[Date],-1,MONTH))
but this will work only with page filter and I need a measure to calculate sum of defects from whole December (because today we have January) versus second measure which will count sum of defects from whole November (current month minus 2)