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
Meerakhanna
Helper I
Helper I

Cumulative counts of items within selected period in chart

Hi,

need help with the formulae for calculating cumulative counts of items which falls in selected period of time between 2 dates.

 

Eg.

NameStartDateEndDate
C110/1/20159/30/2016
C211/1/201510/31/2016
C310/1/20154/1/2017

In the above eg,. I want to show in a chart where the count of items will be shown as cumulative for a period.

Like, the count should be 3 from 10/1/2015 till 9/30/2016, next it will be 2 since the C1 expires by 9/30/2016.  Next it will become 1 after 10/31/2016, For, C2 expires by that time.  If its a line chart, then the line should continue with value 3 then down to 2 after 9/30/2016, then by 1 after 10/31/2016 ...meaning existence of that item for the selected period of time.

Actually the charts have the ability to show up the total counts of names for particular period that too taking in consideration of only 1 date value. 

Can any one help me in this regard? - Thank you!

Let me know if the req,. is still not clear. 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@Meerakhanna here's an alternative => for more see this link 

 

 

Active Count =
CALCULATE (
    DISTINCTCOUNT ( Schedule[Name] ),
    FILTER (
        Schedule,
        Schedule[StartDate] <= LASTDATE ( CalendarTable[Date] )
            && Schedule[EndDate] >= FIRSTDATE ( CalendarTable[Date] )
    )
)

 

 

Here you can see the results you get with both solutions (I've put them in a clustered column chart just for comparison purposes)

 

2016-10-02 - Active Count & Generate.png

 

 

Items in Progress is not showing up in April 2017 in the picture - because you need to be on the DAY level !

 

Hope this also helps! Smiley Happy

 

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

Hi @Meerakhanna

 

An 'events in progress' measure would be appropriate here.

 

Have a look at this paper (page 27 for the best performing DAX code):

http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

 

Adapting to your situation, you can use a measure like this:

Items in Progress =
SUMX (
    GENERATE (
        SUMMARIZE (
            Schedule,
            Schedule[StartDate],
            Schedule[EndDate],
            "Rows", COUNTROWS ( Schedule )
        ),
        INTERSECT (
            LASTDATE ( 'Calendar'[Date] ),
            DATESBETWEEN ( 'Calendar'[Date], Schedule[StartDate], Schedule[EndDate] )
        )
    ),
    [Rows]
)

This measure assumes your table is called Schedule and you also have a 'Calendar' table, but no relationship between the two.

Also, the measure returns the Items in Progress on the last date of the current period (e.g. the last date of the month if you are browsing by month).

 

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Sean
Community Champion
Community Champion

@Meerakhanna here's an alternative => for more see this link 

 

 

Active Count =
CALCULATE (
    DISTINCTCOUNT ( Schedule[Name] ),
    FILTER (
        Schedule,
        Schedule[StartDate] <= LASTDATE ( CalendarTable[Date] )
            && Schedule[EndDate] >= FIRSTDATE ( CalendarTable[Date] )
    )
)

 

 

Here you can see the results you get with both solutions (I've put them in a clustered column chart just for comparison purposes)

 

2016-10-02 - Active Count & Generate.png

 

 

Items in Progress is not showing up in April 2017 in the picture - because you need to be on the DAY level !

 

Hope this also helps! Smiley Happy

 

Thank you!

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.