I have a table of data with the following columns:
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.
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.
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:
Proud to be a Datanaut!
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:
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
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.