cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonArgandona
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
svalen
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
svalen
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.

svalen
Responsive Resident
Responsive Resident

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?

svalen
Responsive Resident
Responsive Resident

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.