Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
LamSar
Helper III
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
andrewpirie
Resolver II
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):
andrewpirie_0-1704271913893.png
 
Resulting table:
andrewpirie_1-1704271950663.png

 

View solution in original post

5 REPLIES 5
andrewpirie
Resolver II
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):
andrewpirie_0-1704271913893.png
 
Resulting table:
andrewpirie_1-1704271950663.png

 

ryan_mayu
Super User
Super User

@LamSar 

pls provide some sample data and expected output





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




zenisekd
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".

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

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

zenisekd_0-1704269947268.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.