Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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!
@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.
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)
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
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) )
In my case, it's a calculated column.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |