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
duncanelliot
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
v-sihou-msft
Employee
Employee

@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,

 

OwenAuger
Super User
Super User

@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?

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
CahabaData
Memorable Member
Memorable Member

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

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.