DAX to accumulate active items throughout slicer of transaction dates

[FStettler]

Hi!

I have a DAX formula that Counts all active items taking into consideration start and finish dates, for a range of different dates set out by a slicer.

Now I need another one that will accumulate the previous Count within a range of dates, for each one of them dates.

So, for instance, a Matrix

1/1/2020, active items = 5, accumulated active items = 5 

2/1/2020, active items = 5, accumulated active items = 10 (are the exact same as on the previous day)

3/1/2020, active items = 6, accumulated active items = 16 (are the exact same as on the previous day + 1)

4/1/2020, active items = 4, accumulated active items = 20 (2 of them are not active any more)

The data model I'm working with, among other items, is as follows:

Table DATE

Column DATES (Active relationship with TRANSACTIONS[POSTING DATE])

Table ITEMS

Column ID (Active relationship with TRANSACTIONS[ITEM])

Column START DATE

Column FINISH DATE

Table TRANSACTIONS

Column POSTING DATE (Active relationship with DATE[DATES]) *date where the transaction was ever posted*

Column ITEM (Active relationship with ITEMS[ID])

Column TRANSACTION DATE **date where the transaction will actually take place**

My DAX formula that Counts the active items is as follows (works just fine):

CALCULATE

( COUNT ( ITEMS[START DATE] ) ,

FILTER ( ITEMS , ITEMS[START DATE] <= MIN(DATE[DATES]) && ITEMS[FINISH DATE] >= MAX(DATE[DATES]) ) )

I have a Matrix where I place DATE[DATES], ACTIVE.

Then I have a date slicer where I place TRANSACTIONS[TRANSACTION DATE]

When playing around with the slicer, I would also need the matrix to show how many accumulated active items are there for every date listed, just like in the example.

Anyone with a possible solution, could you also please briefly explain the mechanism behind it? So I can have a grasp on the logic too.

Thanks a million!

Facundo

[harshnathani]

Hi @FStettler ,

Pls go through this article.

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Sales RT :=

VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date

RETURN

    CALCULATE (

        [Sales Amount],           -- Computes sales amount

        'Date'[Date] <= MaxDate,  -- Where date is before the last visible date

        ALL ( Date )              -- Removes any other filters from Date

    )

Thanks,

Harsh Nathani

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

[FStettler]

Hi @harshnathani,

Thanks for the swift reply but it didn't help me out with my query.

I can't use measures as a filter expression. Also using the FILTER function to be able to use MAX is not working either.

I need to accumulate the same active items even though they've been taken into account in a previous day.

I'm working with room nights on holiday apartments, these are my active Items. Every apartment has its own start and end date of availability. I'm analyzing all room nights to sell within a range of transaction dates.

So for instance, following up on my own example above, from 1/1 to 3/1 I have 16 room nights to sell (5+5+6). Or from 3/1 to 4/1 I have 10 room nights to sell (4+6). Even though they might be related or not to the same active item, when another date is added to the range, that's another room night to sell and needs to be added up to the total accumulated figure.

That's the outcome I need the measure to return.

Thanks again.

Facundo

[harshnathani]

@FStettler ,

Can you share some sample Data 

Regards,

Harsh Nathani