cancel
Showing results for
Did you mean:
Post Patron

## DAX get count from previous existing day

I have following dataset

 Date ProductID 01/01/2017 1 01/01/2017 2 01/01/2017 3 02/01/2017 1 02/01/2017 2 02/01/2017 3 02/01/2017 4 05/01/2017 1 05/01/2017 2 05/01/2017 3 05/01/2017 4 05/01/2017 5 06/01/2017 1 06/01/2017 2 06/01/2017 3 06/01/2017 4

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

13 REPLIES 13
Post Patron

@dpombal

Could you draw the result which you want to receive?

Post Patron

Date                  Measure

01/01/2017 --> BLANK

02/01/2017 --> 3

05/01/2017 --> 4

06/01/2017 --> 5

Post Patron

Date                  Measure

01/01/2017 --> BLANK

02/01/2017 --> 3

05/01/2017 --> 4

06/01/2017 --> 5

Community Champion

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.

Proud to be a Super User!

Responsive Resident

@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:

`CountFromPreviousDay =var PreviousDate = CALCULATE( MAX(Date), FILTER(Table, Date < EARLIER(Date)) )returnCALCULATE( [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.

Dan Malagari
Post Patron

This solution doesn't works for my actual case since if I add 2 slicers, the new calculated column

`CountFromPreviousDay =`

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?

Regards

Post Patron

I added a new column to simulate my case, i need new colum work with an slicer (PruductType)

 Date ProductID ProductType 01/01/2017 1 small 01/01/2017 2 small 01/01/2017 3 big 02/01/2017 1 small 02/01/2017 2 small 02/01/2017 3 big 02/01/2017 4 big 05/01/2017 1 small 05/01/2017 2 small 05/01/2017 3 big 05/01/2017 4 big 05/01/2017 5 big 06/01/2017 1 small 06/01/2017 2 small 06/01/2017 3 big 06/01/2017 4 big

New PBIX link with new data

https://1drv.ms/u/s!Am7buNMZi-gwi2wTE_usJwezZMNE

Frequent Visitor

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.

Post Patron

No unfortunately not, sorry

Post Patron

Is CountFromPreviousDay a new column or a new measure, i am having issues with syntax.

on EARLIER SIDE

CountFromPreviousDay =
var PreviousDate = CALCULATE( MAX('Table'[Date]); FILTER(Table;  'Table'[Date] < EARLIER('Table'[Date]) )
return CALCULATE( [Product Count]; FILTER(Table; Date = PreviousDate) )

Responsive Resident

In my case, it's a calculated column.

Dan Malagari
Post Patron

I can't make  your code work.

Here is a the sample in a PBIX

https://1drv.ms/u/s!Am7buNMZi-gwi2pwTMw6V7RkVr_O

I think it should be better create a measure. All help needed thanks!

Responsive Resident

This is a new Calculated Column.  Then, when adding it to the table visual, you need to disable the summarization on the column.

Dan Malagari

Announcements