Daily count of items that take into account state change

[chokedoke]

Hi All,

I am getting a bit lost in DAX queries and could do with some help in writing my query.

I am trying to create an area chart visulation that shows the current counts of items in a particular state on each day.

The data i have available in my main table is a History table that contains one row for each item and then a row that details the change to that item so for example

Item Name - State          - Previous State - Created Date - Changed Date

Item 1         - new           -  (null)                - 09/06/2018   - 09/06/2018

Item 1         - active        -   new                 - 09/06/2018   - 09/06/2018 

Item 1         - resolved    -  active               - 11/06/2018   - 11/06/2018

So the above should show

9th     0 new | 1 active | 0 resolved

10th   0 new | 1 active | 0 resolved

11th   0 new | 0 active | 1 resolved

My problem is the days where nothing changes (and that could be 5 or 6 days) but the item needs to remain in the count for those days

Any help will be greatly appreciated

Thanks!

[v-yuezhe-msft · Accepted solution]

@chokedoke,

Please check DAX in the PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1Nok3Wm7W3NHwsjxBl5

Reference:
https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Calculate-Active-Tickets-or-Open-Claims/...

Regards,
Lydia

[chokedoke]

Thank you very much for your response. The video is very informative and I am trying to get that working now

The solution should work and i even have most of the calculated columns as part of the data so thats good. I am just having trouble getting the expiaryrow column working properly, although i think thats more to do with my data set. The TFS 2017 workitemhistoryview table seems to not count the rows properly

Hopefully i can work through the problem as its almost their. I will mark your answer as complete as it should resolve my issue so thank you very much