Forum Discussion

schoden's avatar
schoden
Post Partisan
5 years ago
Solved

Show data progress in time

Hi Community,    I have a very simple data from excel sheet. I want to show amount in all months from "Opened"  till "Closed" Status. Essentially I want to show from Jan till July for this ID "a"....
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    schoden ,My bad

    calculate(lastnonblankvalue(Date[Date],max(Table[status])), filter(allselected(Date),Date[Date] <=max(Date[Date])))

amitchandak's avatar
amitchandak
Super User
5 years ago

schoden , with help from a date table, try a measure like

 

filter(lastnonblankvalue(Date[Date],max(Table[status])), filter(allselected(Date),Date[Date] <=max(Date[Date])))

 

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

schoden's avatar
schoden
Post Partisan
5 years ago

Hi amitchandak 

I got this message 

 

 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    schoden ,My bad

    calculate(lastnonblankvalue(Date[Date],max(Table[status])), filter(allselected(Date),Date[Date] <=max(Date[Date])))

    • schoden's avatar
      schoden
      Post Partisan
      5 years ago

      Thanks heap amitchandak  Obtained the result.

       

    • schoden's avatar
      schoden
      Post Partisan
      5 years ago

      Hi amitchandak , 

       

      Sorry for for gap but I am struggling to get lastnonblankvalue for EACH ID in Each MONTH ?

      For example in Jan 2020 there are two IDS - show all value for all each ID.

      Will really appreciate.