cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
duncanelliot Frequent Visitor
Frequent Visitor

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 3
CahabaData New Contributor
New Contributor

Re: Showing how the count evolved over time

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

 

 

www.CahabaData.com
OwenAuger Super Contributor
Super Contributor

Re: Showing how the count evolved over time

@duncanelliot

 

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?


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

Proud to be a Datanaut!




Moderator v-sihou-msft
Moderator

Re: Showing how the count evolved over time

@duncanelliot

 

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,

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 112 members 1,468 guests
Please welcome our newest community members: