cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Track identificator presence/absence over months

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

My initial data looks like:

 

DateSerial
20180201A1223
20180201A1224
20180201A1225
20180301A1223
20180301A1224
20180301N1226
20180401A1223
20180401A1224
20180401M1226

 

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:

 

DateSerialAvailable-1Available+1
20180201A122301
20180201A122401
20180201A122500
20180301A122311
20180301A122411
20180301N122600
20180401A12231 
20180401A12241 
20180401M12260 

 

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

1 ACCEPTED SOLUTION
Responsive Resident
Responsive Resident

Try this:

 

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

View solution in original post

6 REPLIES 6
Responsive Resident
Responsive Resident

Try this:

 

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

View solution in original post

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.

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

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

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

 

Column Name = [Measure you created]

Thanks a lot

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors