Last 6 months average

[ghouse_peer]

Hello Guys,

I need to show last 6 months average Opened and closed tickets in 2 seperate card visuals. 1 card for Opened tickets and 1 card for closed tickets.

Data: Table name: Tickets; Columns: Ticket Number, Opened Date, Closed date

          Table Name: Dates; (Created using calendar)

kindly help me with calculation or solution. 

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[AntrikshSharma]

Try something like this assuming you already have a realtionship between opened date and dates for the below measure.

Opened Tickets =
VAR TimeSpan = 6
VAR MaxVisibleMonth =
    MAX ( Dates[Month Number] )
VAR ListOfDates =
    FILTER (
        ALL ( Dates ),
        Dates[Date] <= MAX ( Dates[Date] )
            && Dates[Month Number] <= MaxVisibleMonth
            && Dates[Month Number] > MaxVisibleMonth - TimeSpan
    )
VAR Result =
    CALCULATE ( [Average Sales], ListOfDates )
RETURN
    Result

For closed one use this.

Closed tickets =
VAR TimeSpan = 6
VAR MaxVisibleMonth =
    MAX ( Dates[Month Number] )
VAR ListOfDates =
    FILTER (
        ALL ( Dates ),
        Dates[Date] <= MAX ( Dates[Date] )
            && Dates[Month Number] <= MaxVisibleMonth
            && Dates[Month Number] > MaxVisibleMonth - TimeSpan
    )
VAR Result =
    CALCULATE (
        [Average Sales],
        ListOfDates,
        USERELATIONSHIP ( Sales[OpenedTicket], Dates[Date] )
    )
RETURN
    Result

[ghouse_peer]

@AntrikshSharma  I have already created relationships between Dates Table and Opened,closed dates in tickets table. Opened date is active and closed date is inactive. I Tried the dax of urs too donno wr m gng wrong. Kindly use the cloumn names from below data and provide me the dax. So tht it will be helpfull. Kindly help.

Tickets

Table Name: Tickets
Opened Date	Closed Date	Ticket Number
1/29/2020	7/1/2020	1122
1/2/2020	6/10/2020	2233
2/15/2020	6/10/2020	1313
1/12/2020	6/15/2020	1212
2/15/2020	6/15/2020	1111
2/29/2020	6/2/2020	3131
2/1/2020	6/2/2020	3434
1/1/2020	6/6/2020	3535
2/2/2020	6/6/2020	3232
2/3/2020	6/6/2020	3233

Dates

Table Name: Dates

Dates

1/29/2020

1/2/2020

2/15/2020

1/12/2020

2/15/2020

2/29/2020

2/1/2020

1/1/2020

2/2/2020

2/3/2020

Thanks in advance

[Anonymous]

Hi @ghouse_peer,

You can take a look at the following link to expand these date field to create a bridge table to link original table, then you can simply analyst records between two date range:

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng

[amitchandak]

@ghouse_peer , You need to join date table with both dates. and then use userelation to choose the dates in formulas given below

For userelation : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Example of 6 month Avg. Add use relation before calculate ends.

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH)) /6
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))  /6

[ghouse_peer]

@amitchandak  I have already created relationships between 'Dates' table and Opened and closed dates. Opened date is active and closed date is inactive. I tried using the dax provided by you. But i am missing somwhere in that. Can u please use this data and provide me the same dax for both opened and closed tickets  using userelationship. Kindly help me for the solution.

Tickets

Table Name: Tickets
Opened Date	Closed Date	Ticket Number
1/29/2020	7/1/2020	1122
1/2/2020	6/10/2020	2233
2/15/2020	6/10/2020	1313
1/12/2020	6/15/2020	1212
2/15/2020	6/15/2020	1111
2/29/2020	6/2/2020	3131
2/1/2020	6/2/2020	3434
1/1/2020	6/6/2020	3535
2/2/2020	6/6/2020	3232
2/3/2020	6/6/2020	3233

Dates

Table Name: Dates

Dates

1/29/2020

1/2/2020

2/15/2020

1/12/2020

2/15/2020

2/29/2020

2/1/2020

1/1/2020

2/2/2020

2/3/2020

thanks in advance.