Backlog Static value

[ghouse_peer]

Hello Guys,

I have used backlog formula and took it into a visual. values are showing perfectly but after June 2020 the recent backlog value is reflecting for all the months. That should not happen in visual, It should stop till june. If at all july data is added then it needs to calculate accordingly.

Dax: Backlog=

CALCULATE([opened Date]-[Closed date],FILTER(ALLSELECTED(Dates),Dates[Date]<=max(Dates[Date])))

Red line which is backlog count.

From July to december it is showing june value. I dont want that. As we dont have remaining months data. It should stop till june. Kindly help. 

Thanks in advance.

[amitchandak]

@ghouse_peer  , Try

Dax: Backlog=
CALCULATE([opened Date]-[Closed date],FILTER(ALLSELECTED(Dates),Dates[Date]<=max(Backlog[Date])))

Use date from you table in the max

or

Dax: Backlog=

var _max = maxx(allselected(Dates),Dates[Date])

retunr
CALCULATE([opened Date]-[Closed date],FILTER(ALLSELECTED(Dates),Dates[Date]<=max(Backlog[Date])),

Backlog[Date] <=_max

)

[ghouse_peer]

@amitchandak  what is this Backlog[Date] in the  measure which you have provided. I do not have any seperate date for backlog in my model.

If at all i want to use date in Max which date i have to use.

Pls elaborate and help.

[amitchandak · Accepted solution]

@ghouse_peer , That is Date in you table. If use max date in the table will stop at max date in the table 

In place of backlog date use date from table 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

Like in second case I has use Sales date from sales table

[ghouse_peer]

@amitchandak  Thanks for the solution. Somehow i was not having seperate date column in tickets table. Again i created a new column with difference of opened and closed date. So i got seperate date column. I used that and got the required solution.

[ghouse_peer]

@amitchandak  I dont have any specific date column in Tickets table. only i have ticket opened date, Ticket closed date, Ticket number. 

I have seperate date table which contains all dates from the  model created using calendarauto().

According to u when i changed the date to Opened date in max and viceversa, the value is getting wrong and visual is not in the format which is required.

i jst want that value to stop there instead of showing default  for alll months. Kindly help how to overcome this.