Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Continuous cumulative count based on discrete dates

Hi all,   I have a table with customer IDs, and the date of their first, second, third, and fourth car purchases. (Some customers might not have four purchases, for them the dates would be 2099, da...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Go To Home > Transform Data.  click on The Categories query and double click on Source.  Make changes there and click on OK.  Click on Close and Apply.

    Hope this helps.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous I would start by unpivoting the four columns after your Customer_ID column. Then you will need something like the following: 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

Of course, yours will be a variation where you will need to find the "previous value" for the purchase prior to the next purchase:

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