calculation of new records per status

[Anonymous]

hi all,

i've been facing a while in the following calculation.

I have a table which contains for each store the status (open, closed or opening -it's before the opening), and the date of check.

Now: i'd like to calculate the followings:

• a chart which shows the trend -let's say per month- of the new open stores (and closed). Only the new/closed, separately. I'm not interested in how many stores are open, here the focus is how many in a month have been opened.
• from the chart, i should be able to click on the element and to view in the table the details. Ie. i click on the new open stores in March, and in the table I view the stores. (not all the "open" stores, but only the new opens).

below the link with a sample

https://drive.google.com/file/d/1B68eDEGq0Gr3Z46ozuFxm-4dgX_riSiO/view?usp=sharing

do you think is it something feasible with power bi?

thanks in advance, and for further details please do not hesitate!

[Anonymous]

adding:

• i can calculate the number of the new store opened/closed in the last month by creating an automatic table, but i need to have for all the months
• dimensions like "date of open" and "date of close" are not available/consistent

[v-jiascu-msft]

Hi @Anonymous,

Please download the demo from the attachment. 

Since you'd like to filter them in the visual, I think we need a calculated column.

ifNewOpen =
VAR existedStores =
    CALCULATETABLE (
        VALUES ( store_progress[store] ),
        FILTER (
            ALL ( 'store_progress' ),
            'store_progress'[date] <= EOMONTH ( EARLIER ( [date] ), -1 )
        )
    )
RETURN
    IF ( [store] IN existedStores, "No", "Yes" )

Then count the [ifNewOpen] directly or create a measure.

Measure =
VAR existedStores =
    CALCULATETABLE (
        VALUES ( store_progress[store] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), -1 )
        )
    )
RETURN
    CALCULATE (
        COUNT ( store_progress[store] ),
        FILTER ( ALL ( store_progress[store] ), NOT [store] IN existedStores )
    )

A date table is always necessary for the time intelligence functions.

Best Regards,

[Anonymous]

hi , thank you for your answer!

unfortunately, looking at the results, the open status is not working really well.

highlighted where the Yes is expected to be shown, while in Red the wrong attribution. 

basically, there'd be to create a formula working at a cell level which, for the first time a row made by date&status, has the value "open".

any advice?

best

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

You mean we just need to consider the [date] and the [status]. If so, try this one, please.

ifNewOpen =
VAR existedStores =
    CALCULATETABLE (
        VALUES ( store_progress[store] ),
        FILTER (
            ALL ( 'store_progress' ),
            'store_progress'[date] <= EOMONTH ( EARLIER ( [date] ), -1 )
                && store_progress[status] = "open"
        )
    )
RETURN
    IF ( [store] IN existedStores || NOT [status] = "open", "No", "Yes" )

Best Regards,

[Anonymous]

it works, that's great! you've made my day 🙂

thanks a lot for your help

[Anonymous]

@ v-jiascu-msft