Forum Discussion
Running Count based on Data Version, Product, and Request Date
Hello Everyone,
I am new to all this and getting pretty twisted up in what I think should be an easy solution after weeks of trying to figure it out for myself on this community forum and other sources.
My data set is much larger, but here is an example of what I am looking to do. I am trying to count how long a Requested Date has been in the data set for a Product on any specific Data Version date. This is to establish if the Request Date has been forecasted long enough to count contractually. We get new Data Versions every weekday, but the count according to the letter of the contract includes all days (weekends included).
In this example the Product Red first showed up in the data on 1/2/2023 with a Req Date of 4/1/2023. The last time it had a Req Date of 4/1/2023 was on 1/10/2023, so it had a count of 9 days as a stable forecast. The forecast pushed to 5/1/2023 on 1/11/2023.
I am trying to write a measure that does this running count so I can create a simple report where I can pull any Data Version date and show how many days the Req Date for each product had been stable.
Hopefully that makes sense.
| Data Version | Product | Req Date | Count Req Date | ||
| Monday | 1/2/2023 | Red | 4/1/2023 | 1 | |
| Tuesday | 1/3/2023 | Red | 4/1/2023 | 2 | |
| Wednesday | 1/4/2023 | Red | 4/1/2023 | 3 | |
| Thursday | 1/5/2023 | Red | 4/1/2023 | 4 | |
| Friday | 1/6/2023 | Red | 4/1/2023 | 5 | |
| Monday | 1/9/2023 | Red | 4/1/2023 | 8 | |
| Tuesday | 1/10/2023 | Red | 4/1/2023 | 9 | |
| Wednesday | 1/11/2023 | Red | 5/1/2023 | 1 | |
| Thursday | 1/12/2023 | Red | 5/1/2023 | 2 | |
| Friday | 1/13/2023 | Red | 5/1/2023 | 3 |
Thanks!
Anonymous My bad, that's what I get for not setting up the model and testing properly. I believe this should do it and PBIX is attached below signature. Thanks for sharing the PBIX, really helps.
Count Req Date Column = VAR __Product = [Product] VAR __ReqDate = [Req Date] VAR __Date = [Data Version] VAR __MinDate = MINX(FILTER(ALL(Table1),[Product] = __Product && [Req Date] = __ReqDate),[Data Version]) VAR __Result = ( __Date - __MinDate ) * 1. + 1 RETURN __Result
4 Replies
Brilliant! Worked perfectly. Thanks for the guidance. Slowly making progress on my project.
Cheers!
- Greg_Deckler
Community Champion
Anonymous Try:
Count Req Date Column = VAR __Product = [Product] VAR __ReqDate = [Req Date] VAR __MinDate = MINX(FILTER(ALL('Table'),[Product] = __Product && [Req Date] = __ReqDate),[Req Date]) VAR __Result = ( __ReqDate - __MinDAte ) * 1. RETURN __ResultBasically a variation of MTBF: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousYou might also be able to use a variation of Cthulhu, which is sort of purpose built for this kind of thing but thought you might be able to get away with something that is a little easier. Cthulhu - Microsoft Power BI Community
Hi Greg_Deckler , thanks for the fast response!
I gave it a try, must be doing something wrong. Below is a link to a PBI file with the data and the Measure as well as trying it as a calculated column. When I did a measure, it didn't let me set the VAR as a reference to the table, so I had to use a SELECTVALUE measure.
I am thinking this was supposed to be a calculated column, so I did that as well. But I am not getting the expected results. Thoughts?https://drive.google.com/file/d/1zp3zOvDQI7qTCQIhngVblnwQT7W_DFgq/view?usp=share_link
I really like the MTBF example and will play with that some more, could be really handy in my world as well.
The Cthulhu approach was pretty complicated, I will give it a look on a smaller set of data. When I tried to apply it to my real data which has >2Million rows, it crashed, likely user error:-)Thanks again!
- Greg_Deckler
Anonymous My bad, that's what I get for not setting up the model and testing properly. I believe this should do it and PBIX is attached below signature. Thanks for sharing the PBIX, really helps.
Count Req Date Column = VAR __Product = [Product] VAR __ReqDate = [Req Date] VAR __Date = [Data Version] VAR __MinDate = MINX(FILTER(ALL(Table1),[Product] = __Product && [Req Date] = __ReqDate),[Data Version]) VAR __Result = ( __Date - __MinDate ) * 1. + 1 RETURN __Result