Forum Discussion
Showing how the count evolved over time
Hi there,
I have a table of data with the following columns:
| CREATED_ON |
| Locations_ID |
| Offer_ID |
| DELETED |
| LAST_MODIFIED |
The scenario is that locations can list offers:
- When a new offer is listed at a location, a new entry is made in the table
- When an offer is deleted, the deleted column is set to 1, Last_modified set to the current date/time and the row will never change again.
What I would like to do is show on a bar chart / line chart how many 'live' offers there are at any point in time. I think this could be achieved using DAX. Any help would be greatly appreciated.
Many thanks,
3 Replies
Yes definitely do-able. It can be done at the visual level using a filter applied to the Delete field. Or it could be done at the table level in creating a new table of just Live records....
If you expect to include visuals including Delete records - such as counts or comparisons of Deletes to Live; then working with the whole table and tactically applying a visual filter makes sense.
On the other hand if all you ever will do is report on Live records - and Deletes are just clutter - then getting the new table upfront can be easier.....
Events in progress!
Have a look at this paper, page 16 onwards, for some ideas on DAX code. The paper contains DAX queries but the logic is basically the same for a measure.
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf
This has come up several times on this forum and the Mr Excel Power BI forum if you search for 'events in progress'.
Questions to think about in your situation that would affect the DAX code:
- Is the 'end date' of any offer unbounded until DELETED = 1?
- What do you want your measure to return for a period of dates, e.g. if browsing by month? Live offers on the last date of the period, or all offers that were live at any time during the period?
In this scenario, I suggest you combine the start date and end date onto same row for each individual offer. Then you can calculate "active" offers within date range:
Active Offers:=CALCULATE(COUNTROWS(DimOffer), FILTER(DimOffer, ([StartDate] <= LASTDATE(DimDate[Datekey]) && [EndDate>= FIRSTDATE(DimDate[Datekey]))))
Please also refer to articles below:
Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell
PowerPivot Use Case: Getting the active products between a date range using DAX
Regards,
