Track identificator presence/absence over months

[JonArgandona]

I want to track the presence/absence of a serial number over different months.

My initial data looks like:

Date	Serial
20180201	A1223
20180201	A1224
20180201	A1225
20180301	A1223
20180301	A1224
20180301	N1226
20180401	A1223
20180401	A1224
20180401	M1226

I want to know if a serial number existed in the previous month or if it will exist in the next month. The output I'd like to obtain is:

Date	Serial	Available-1	Available+1
20180201	A1223	0	1
20180201	A1224	0	1
20180201	A1225	0	0
20180301	A1223	1	1
20180301	A1224	1	1
20180301	N1226	0	0
20180401	A1223	1
20180401	A1224	1
20180401	M1226	0

 Ideally, I would like to use a M function to get that table, but a DAX expression would suffice.

[svalen · Accepted solution]

Try this:

Available -1 =
CALCULATE (
    COUNT ( 'TABLE'[Serial] ),
    FILTER ( 'TABLE', DATEADD ( 'TABLE'[Date], -1, MONTH ) )
)
    > 0

[JonArgandona]

The expression works correctly. However, I want to use it for filtering purposes in the visualisation and I don't think I can do so. I'm thinking about using an R query to create the value, but, I rather do it as a M query.

[svalen]

Try adding a column with the measure you just created and filter with that.

[JonArgandona]

Sorry, I'm quite new to Power BI. What are the steps to create a varable out of a calculated measure?

[svalen]

Add a new column to the table (right click, add column on the right panel) and adapt:

Column Name = [Measure you created]

[JonArgandona]

Thanks a lot