Measure to return the dates on which a shop is open but there are no sales.

[LamSar]

Hello everyone,

I need to create a measure that returns the dates on which a shop is open but there are no sales. 

I have 2 fact tables:
- ShopCalendar is the fact table that indicates when a Shop is open. It contains SK_Date, SK_Shop, IsOpen
- Sales: Contains the sales data. It contains, SK_Date, SK_Shop, SalesAmount. I need to create a DAX measure that returns the dates on which there is no data in Sales, but according to the ShopCalender the shop should be open.

Then I need to add this measure to a table visual that contains the shop and the measure that returns the dates on which on which there should be sales. 

Any ideas on how to achieve this?

[andrewpirie · Accepted solution]

zenisekd's approach is how I would normally do this as I've found the date as a separate column makes it easier to read compared to a list of dates.

If your use-case does mean that a measure that returns the dates where the shop has no sales is preferrable, note that a measure returns a single value, so you'd need to somehow merge all of those dates into a single result value.

Here's an example measure that returns the list of dates for the currently visible shop as a comma-separated list:

Shop Open No Sales =

VAR _shopOpenDates = SELECTCOLUMNS(

    FILTER(

        ShopCalendar

        , ShopCalendar[IsOpen]

    )

    , "ShopCalendarDate", [SK_Date]

    , "ShopName", [SK_Shop]

)

VAR _shopOpenDatesWithNoSalesText =  CONCATENATEX(

        FILTER(

            _shopOpenDates

            , ISEMPTY(

                FILTER(

                    Sales

                    , Sales[SK_Date] = [ShopCalendarDate]

                    && Sales[SK_Shop] = [ShopName]

                )

            )

        )

        , FORMAT([ShopCalendarDate], "dd/MM/yyyy")

        , ", "

    )

RETURN IF(

    HASONEVALUE(ShopCalendar[SK_Shop])

   , _shopOpenDatesWithNoSalesText

    , BLANK()

)

This would return blank if it's put in a cell with no shop filtered (e.g. a card or the total row, where multiple shops are visible).

Data model (no relationships):

Resulting table:

[ryan_mayu]

@LamSar 

pls provide some sample data and expected output

[zenisekd]

@LamSar perhaps you could create a measure, something like: 
Create a table, where you put the dates and then use the "SaleAmount" as a visual filter, setting the condition to be "blank" or =0.

Or create a measure:

WasSale= if(sum('Sales'[SalesAmount])>0,"Sale", "No Sale")
Then you create a table where you put the "Date" and "WasSale" and display only "WasSale".

[LamSar]

That will not work for me, because for some dates + Shops, there are no rows in the sales table

[zenisekd]

It will work as long as you select option "show items with no data".