Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!




@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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.