I have following dataset
I need to get the count of product ID for each date the previous
Product Count = COUNT('Table'[ProductID])
Below measure fails due to gaps on my dates
Previous Day Count = CALCULATE([Product Count];PREVIOUSDAY('Table'[Date]) )
I need to get the previous existing count
You need a date table connected to your data table. That table should have a continuous list of all dates for the length of time covered by your report. Create a relationship with your date column on your data table and use the date table's date column in that PREVIOUSDAY formula.
@KHorseman's approach is a best practice - a date table is fundamental if you're going to be doing a lot of date-based calculations.
That said, you could create a calculated column to do something like this:
var PreviousDate = CALCULATE( MAX(Date), FILTER(Table, Date < EARLIER(Date)) )
CALCULATE( [Product Count], FILTER(Table, Date = PreviousDate) )
The PreviousDate variable will grab the largest Date that is before the date we're currently looking at. We then use this in our calcuation.
This solution doesn't works for my actual case since if I add 2 slicers, the new calculated column
doesn't takes them into account.
Is it any way to make this work as a live measure calculated on runtime based on filters applied?
I added a new column to simulate my case, i need new colum work with an slicer (PruductType)
New PBIX link with new data
I was just wondering if you ever found a solution to this 'grouped' previous count. I've come across the same issue. Whilst it's pretty straightforward in SQL or Excel or Access it seems mindbendingly tricky im Power BI.
Is CountFromPreviousDay a new column or a new measure, i am having issues with syntax.
on EARLIER SIDE
var PreviousDate = CALCULATE( MAX('Table'[Date]); FILTER(Table; 'Table'[Date] < EARLIER('Table'[Date]) )
return CALCULATE( [Product Count]; FILTER(Table; Date = PreviousDate) )
I can't make your code work.
Here is a the sample in a PBIX
I think it should be better create a measure. All help needed thanks!
I just downloaded your .PBIX and took a look. Here were my results:
This is a new Calculated Column. Then, when adding it to the table visual, you need to disable the summarization on the column.