Identify missing months in for each product

[Anonymous]

Hi All,

I have different store codes in the report. We get bill invoices almost every month. But some months are missing. How do I identify for each store, the missing month.

In example above, For store ending with 578, there are no bills for November and Dec. How do I populate only rows which are missing. Is there any DAX functions which can do this or any logics behind to do it in DAX. Appreciate you help.

Thank you

[Jos_Woolley]

Might be a tad more efficient with EXCEPT:

Missing Months =
VAR TestTable =
    SUMMARIZE ( GENERATESERIES ( 1, 12 ), [Value] )
RETURN
    CALCULATE (
        CONCATENATEX (
            EXCEPT ( TestTable, VALUES ( 'Table'[Calendar Month of Year] ) ),
            [Value],
            " "
        ),
        ALL ( 'Table'[Calendar Month of Year] )
    )

Regards

[Jos_Woolley]

If you want to include the measure in a visual which also contains the Calendar Month of Year field:

Missing Months =
VAR TestTable =
    SUMMARIZE ( GENERATESERIES ( 1, 12 ), [Value] )
RETURN
    CALCULATE (
        CONCATENATEX (
            TestTable,
            IF ( NOT ( [Value] IN VALUES ( 'Table'[Calendar Month of Year] ) ), [Value] ),
            " "
        ),
        ALL ( 'Table'[Calendar Month of Year] )
    )

 Regards

[Jos_Woolley]

Hi,

Try this measure:

Missing Months =
VAR TestTable =
    SUMMARIZE ( GENERATESERIES ( 1, 12 ), [Value] )
RETURN
    CONCATENATEX (
        TestTable,
        IF ( NOT ( [Value] IN VALUES ( 'Table'[Calendar Month of Year] ) ), [Value] ),
        " "
    )

which, when placed in a simple table visual with the Store Code and Calendar Year, will provide a list of missing months for each Store/Year.

Regards

[Anonymous]

Hi Jos,

Tried your solution. The output is showing all months except month present for that row. The  month number 10 is missing in this series. Instead, can I only get number 11,12 in  the series for each store code. 

This is the DAX I have used. 

Missing month =

VAR TestTable =

SUMMARIZE ( GENERATESERIES ( 1, 12 ), [Value] )

RETURN

CONCATENATEX (

TestTable,

IF ( NOT ( [Value] IN VALUES ( '__Bill By Month'[Calendar Month Of Year]) ), [Value] ),

" "

)

[Fowmy]

@Anonymous 

You can do it in Power Query as well.
I made a sample dataset with records missing for Apr and May for Store 100 and May for 200. I also attached the file for you.

Result in a new table

[mahoneypat]

The best way to do that is to create a separate Date table, make a relationship between the Date columns (1:Many from Date table to your table above), and then create a measure that returns a value if there are no rows for that month.

Creating a simple date table in DAX - SQLBI

Pat