Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 sour...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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
  __Result

Basically 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 - __Previous

 

You 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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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