cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpombal
Post Patron
Post Patron

DAX get count from previous existing day

I have following dataset

 

DateProductID
01/01/20171
01/01/20172
01/01/20173
02/01/20171
02/01/20172
02/01/20173
02/01/20174
05/01/20171
05/01/20172
05/01/20173
05/01/20174
05/01/20175
06/01/20171
06/01/20172
06/01/20173
06/01/20174

 

 

 

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
bsas
Post Patron
Post Patron

@dpombal

 

Could you draw the result which you want to receive?

Date                  Measure

01/01/2017 --> BLANK

02/01/2017 --> 3

05/01/2017 --> 4

06/01/2017 --> 5

 

Date                  Measure

01/01/2017 --> BLANK

02/01/2017 --> 3

05/01/2017 --> 4

06/01/2017 --> 5

KHorseman
Community Champion
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




malagari
Responsive Resident
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)) )

return
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.

Dan Malagari
Consultant at Headspring

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

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

 

DateProductIDProductType
01/01/20171small
01/01/20172small
01/01/20173big
02/01/20171small
02/01/20172small
02/01/20173big
02/01/20174big
05/01/20171small
05/01/20172small
05/01/20173big
05/01/20174big
05/01/20175big
06/01/20171small
06/01/20172small
06/01/20173big
06/01/20174big

 

 New PBIX link with new data

 

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

pbi_m1
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.

No unfortunately not, sorry

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) )

malagari
Responsive Resident
Responsive Resident

In my case, it's a calculated column.

Dan Malagari
Consultant at Headspring

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!

malagari
Responsive Resident
Responsive Resident

I just downloaded your .PBIX and took a look.  Here were my results:

 

Screen Shot 2017-09-22 at 11.33.40 AM.png

 

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

 

Screen Shot 2017-09-22 at 11.35.41 AM.png

Dan Malagari
Consultant at Headspring

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors