Cumulative count over time, with previous month addition and subtractions

[Anonymous]

I have tricky series of calculations that I just can't get right, 

I have a table with an ID, opening date and closing date for stores

I then have a more updated table with just opening dates

and the same but for closings

Let's call the tables: Stores, Openings, Closings.

I want to create a matrix that for a single selected year shows me the following:

	January	February	March	April	May	June
Active	199	198	200	200	199	199
Opened		2		1		1
Closed	-1			-2
Total	198	200	200	199	199	200

I've managed to create a measure that correcly shows me the current active per year/month, without considering the opened/closed from the other two tables. 

"Total" would easily be "Active + Openings + Closed"

Which I've then added to a new measure to get these from the previous month, so now that trails correctly. 

But now the issue is how do I get the "Total" to also get the new Active? which is dependent on total? 😄

Edit: it didn't actually really work as intended, 

my "active with openings&closings" that checks the total from previous month isn't properly working... 

[Anonymous]

Hi @Anonymous ,

Based on the testing, creating the new table for matrix row and creating the relationship between tables.

Then, try using the following DAX formula to calculate the store number for each month.

Active = 
CALCULATE(
    COUNTROWS(Store),
    FILTER(
        ALLEXCEPT(Store, Store[Month]),
        Store[opening date] >= MIN('Table'[Date]) &&
        (ISBLANK(Store[closing date]) || Store[closing date] < MAX('Table'[Date]))
    )
)

Opened = 
CALCULATE(
    COUNTROWS(Openings),
    FILTER(
        ALLEXCEPT(Openings, Openings[Month]),
        Openings[Opening Date] <= MAX('Table'[Date]) &&
        Openings[Opening Date] > MIN('Table'[Date])
    )
) + 0

Closed = 
CALCULATE(
    -COUNTROWS(Closings),
    FILTER(
        ALLEXCEPT(Closings, Closings[Month]),
        Closings[closing date] <= MAX('Table'[Date]) &&
        Closings[closing date] > MIN('Table'[Date])
    )
) + 0

Measure switch = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table statu'[status]) = "Active", [Active],
    SELECTEDVALUE('Table statu'[status]) = "Openings", [Opened],
    SELECTEDVALUE('Table statu'[status]) = "Closings", [Closed],
    [Active] + [Closed] + [Opened]
)

Drag the status column to the matrix row, drag the month column to the matrix column and drag the switch measure to the values.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous thanks for the reply and assist!

The active measure doesn't give me the full number of active stores for that month when I filter on a year, only the ones with an opening date that year-month. So I need it to be cumulative up until the first date of selected year for it to work properly I think. And same with closing dates, I want to not count closing dates for selected year, but I want all closing dates before the selected year to be counted in so I get the correct amount of open stores. 

And maybe I misunderstand the switch measure, but I don't have a "status" column sadly. And I think that would also need to be calculated in some way since it depends on what date you're looking at the store? 

[Anonymous]

Hi ,

If you want below result to show up in the matrix, you need to create columns containing active, openings, closings as the measure can't be put inside the matrix rows. You could use the ALL function to remove the year filter, and then use <= to determine all dates before the selected year.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

I got it working, forgot to update here: using the following measures, and having them as rows in a matrix is possible when enabling the option "Switch values to rows" under visual - values - options 🙂  

cumulative active:
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR CurrentMonth = MAX('DateTable'[Month])

VAR PreviousActive =
    CALCULATE(
        [Active],
        FILTER(
            ALL('DateTable'), 
            'DateTable'[Month] = CurrentMonth - 1 &&
            'DateTable'[Year] = SelectedYear
        )
    )

RETURN
PreviousActive + 
CALCULATE(
    [Openings] + [Closings (negative)],
    FILTER(
        ALL('DateTable'), 
        'DateTable'[Month] < CurrentMonth &&
        'DateTable'[Year] = SelectedYear
    )

Running Total = 
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR CurrentMonth = MAX('DateTable'[Month])

RETURN
CALCULATE(
    [Active] + 
    SUMX(
        FILTER(ALL('DateTable'), 'DateTable'[Month] <= CurrentMonth &&
          'DateTable'[Year] = SelectedYear),
        [Openings] + [Closings (negative)]
    )
)