DAX measure to calculate how many days each store was open

Question

I am trying to write a measure that calculates the number of days each store was open each month.

I have Open Dates for each store and the value that this measure should take for each store is either all days of the month such as 31 days in January if the store opened before January OR the difference between end of month and open date if the store opened in the same month.

I tried the following measures:

Calculated Open Date = LASDATE(Store[Open Date])

Days Open = Calculate(INT(LASTDATE(Calendar[Date]) - [Calculated Open Date],
                          FILTER(Store, [Calculated Open Date] <= MAX(Calendar[Date]))

You cant simply create a measure that count the distict open date. If you connect the open date to a date dimension when you filter by month an year IT counts the number of open date of the store? — nicolò grando
I need to trend this over time, so the output would be average sales per day for each month by store (line chart). I cant filter by month. I am not sure if that you get what i am trying to achieve? — Dab
I have another question, you have in the table the days when the store is closed and the sales amount = 0 ? if you havent you can do a simple average of sales amount. (it's the same of sales / number of days) — nicolò grando

Lukasz P. Lukasz P. · Accepted Answer · 2016-03-16T17:08:34

Making an assumption that you will put Calendar[Date]->Month on the X-axis of your chart, and the series will be a measure Avg Sales. Then you want something like:

Avg Sales := Sum(Store[Sales])/DaysOpen
DaysOpen := COUNTROWS(CalculateTable(Calendar[Date], Store[Sales] <> Blank())

HTH.

DAX measure to calculate how many days each store was open

1 Answers