Days open in month (for stores)

[domdre35]

Hello everyone,

to begin with, thank you for beeing part of this cumminty 🙂

Since several days I am working now on this issue.

I have a list with stores and open dates (see beloew) and a DimDate Table

Store_ID         Open Date

Store 1           15.01.2018  

Store 2           20.02.2018

Store 3           10.03.2018

I want to calculate for each month the total amount of days a store was opened (instead od dynamic 30/31 days a fixed value of 30 for full month is ok).

Example

January                    February               March                        or    Year (Jan-March)

15+0+0=15            30+10+0=40       30+30+20=80                  15+40+80=135

Is this possible, if yes, could you please help me with dax measure? Thank you very much in advance!

DoM

[v-shex-msft · Accepted solution]

Hi @domdre35,

Please take a look at following measure formula if it suitable for your requirement:

Remain days =
VAR _start =
    FIRSTDATE ( VALUES ( 'Calendar'[Date] ) )
VAR _end =
    LASTDATE ( VALUES ( 'Calendar'[Date] ) )
RETURN
    SUMX (
        FILTER (
            ALL ( Store ),
            [Open Date] IN CALENDAR ( _start, _end )
                || [Open Date] <= _start
        ),
        DATEDIFF ( MAX ( [Open Date], _start ), _end, DAY )
    )

Regards,

Xiaoxin Sheng