cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

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

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?

1 ACCEPTED SOLUTION
Resolver II

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:

5 REPLIES 5
Resolver II

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:

Super User

pls provide some sample data and expected output

Proud to be a Super User!

Super User

@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".
Helper III

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

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors