Forum Discussion

ghouse_peer's avatar
ghouse_peer
Post Patron
6 years ago
Solved

Backlog Static value

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.
 
 
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      ghouse_peer
      Post Patron
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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