Forum Discussion

you7777's avatar
you7777
Frequent Visitor
1 year ago
Solved

How to return distinct labels when comparing months?

I have a trending dataset where I'm trying to show which labels were added or removed by each month. Basically, I take a snapshot of an environment every month and store the data into a DB. Can someone write an example of a query that will show you distinct values when it compares one month to another? Here is an example of my data:

 

November:

 

 

December:

 

January:

 

Please write a query that shows me the unique labels for each month. Ideally states which ones were added and which ones were removed. Thanks!

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi you7777 ,

     

    Thanks lbendlin for the quick answer and solution. I have some other ideas to add:

    (1) This is my test data.  

     

    (2) We can create tables.

     

    Table 2 = 
var _min_date=MINX(ALL('Table'),[DB Loading Date])
var _table1= SELECTCOLUMNS('Table',[App Label],[DB Loading Date],[Status])
RETURN ADDCOLUMNS(FILTER('Table',[DB Loading Date] <> _min_date),"pre_date",EDATE([DB Loading Date],-1))
    Table 3 = 
var _table1= SELECTCOLUMNS('Table',[App Label],[Status],"Date",[DB Loading Date])
var _table2= SELECTCOLUMNS('Table 2',[App Label],[Status],"Date",[pre_date])

var _cancelled=ADDCOLUMNS( EXCEPT(_table1,_table2),"_status","cancelled","DB Loading Date",EDATE([Date],1))
var _add=ADDCOLUMNS(EXCEPT(_table2,_table1),"_status","add","DB Loading Date",EDATE([Date],1))
var _table3=SELECTCOLUMNS(_cancelled,[App Label],"Status",[_status],[DB Loading Date])
var _table4=SELECTCOLUMNS(_add,[App Label],"Status",[_status],[DB Loading Date])

var _table5= SELECTCOLUMNS(FILTER(UNION(_table3,_table4,'Table'),[Status]="active"),[App Label],[DB Loading Date])
var _table6= SELECTCOLUMNS(FILTER(UNION(_table3,_table4,'Table'),[Status]="add"),[App Label],[DB Loading Date]) 
var _table7= UNION(SELECTCOLUMNS(ADDCOLUMNS(EXCEPT(_table5,_table6),"Status","active"),[App Label],[status],[DB Loading Date]),_table4,_table3)
var _max_date=MAXX(_table7,[DB Loading Date])
RETURN
    FILTER(_table7,[DB Loading Date]<>_max_date)

     

    (3) Then the result is as follows. December saw a decrease of one compared to November, and January saw three new additions compared to December.

     

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi you7777 ,

     

    Thanks lbendlin for the quick answer and solution. I have some other ideas to add:

    (1) This is my test data.  

     

    (2) We can create tables.

     

    Table 2 = 
var _min_date=MINX(ALL('Table'),[DB Loading Date])
var _table1= SELECTCOLUMNS('Table',[App Label],[DB Loading Date],[Status])
RETURN ADDCOLUMNS(FILTER('Table',[DB Loading Date] <> _min_date),"pre_date",EDATE([DB Loading Date],-1))
    Table 3 = 
var _table1= SELECTCOLUMNS('Table',[App Label],[Status],"Date",[DB Loading Date])
var _table2= SELECTCOLUMNS('Table 2',[App Label],[Status],"Date",[pre_date])

var _cancelled=ADDCOLUMNS( EXCEPT(_table1,_table2),"_status","cancelled","DB Loading Date",EDATE([Date],1))
var _add=ADDCOLUMNS(EXCEPT(_table2,_table1),"_status","add","DB Loading Date",EDATE([Date],1))
var _table3=SELECTCOLUMNS(_cancelled,[App Label],"Status",[_status],[DB Loading Date])
var _table4=SELECTCOLUMNS(_add,[App Label],"Status",[_status],[DB Loading Date])

var _table5= SELECTCOLUMNS(FILTER(UNION(_table3,_table4,'Table'),[Status]="active"),[App Label],[DB Loading Date])
var _table6= SELECTCOLUMNS(FILTER(UNION(_table3,_table4,'Table'),[Status]="add"),[App Label],[DB Loading Date]) 
var _table7= UNION(SELECTCOLUMNS(ADDCOLUMNS(EXCEPT(_table5,_table6),"Status","active"),[App Label],[status],[DB Loading Date]),_table4,_table3)
var _max_date=MAXX(_table7,[DB Loading Date])
RETURN
    FILTER(_table7,[DB Loading Date]<>_max_date)

     

    (3) Then the result is as follows. December saw a decrease of one compared to November, and January saw three new additions compared to December.

     

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.